CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsqlselect-case
Jakub Licznerski
Jakub Licznerski

Reputation: 1088

How to create a function which return a boolean depending on a select statement result

I have a table(Id, FK, Date1, Date2) and I need to create a function that will return a boolean value. Generally I want to select records with matching the given FK and check some additional statements. I want to return true if the select statement returns 0 rows and false otherwise. I've already written this code:

CREATE FUNCTION isAlreadyTaken(FK INT4, Date1 DATE, Date2 DATE)
  RETURNS BOOLEAN
AS $$
BEGIN
  CREATE TEMP TABLE helper ON COMMIT DROP AS SELECT COUNT(table.FK) AS quant 
    FROM table
    WHERE table.FK = FK AND table.Date2 IS NULL;
  SELECT CASE
    WHEN helper.quant > 0
    THEN FALSE
    ELSE TRUE 
  END;
END
$$ LANGUAGE plpgsql;

The code is compiling, and can be executed. But when I invoke the function in CHECK statement it returns an ERROR [42P01]. Is the solution near the actually working one, or I've completely lost my way ?

Upvotes: 4

Views: 14285

Answers (1)

wildplasser
wildplasser

Reputation: 44250

COUNT(...) > 0 WHERE ... is equivalent to EXISTS(select ... where ...) , and EXISTS(table expression) already yields a boolean:

EXISTS (
 SELECT *
 FROM table t
 WHERE t.FK = FK AND t.Date2 IS NULL
 )

Putting that into your query (Note the Date1 and Date2 arguments appear to be unused...)

CREATE FUNCTION isAlreadyTaken1(FK INT4, Date1 DATE, Date2 DATE)
  RETURNS BOOLEAN
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM table t
    WHERE t.FK = FK 
         AND t.Date2 IS NULL    -- <<-- this looks nonsensical
        );
END
$$ LANGUAGE plpgsql;

And, as @a_horse_with_no_name commented: you don't even need plpgsql, just plain SQL will do:

CREATE FUNCTION isAlreadyTaken1a(FK INT4, Date1 DATE, Date2 DATE)
  RETURNS BOOLEAN
AS $func$
  SELECT EXISTS (
    SELECT 1
    FROM table t
    WHERE t.FK = FK
         AND t.Date2 IS NULL    -- <<-- this looks nonsensical
        );
$func$ LANGUAGE sql;

BTW: I inverted the logic. IMO allreadyTaken means count() > 0.

Upvotes: 9

Related Questions