vendredi 1 avril 2016

SQL - SELECT with inner IF or CASE

Trying to have a row that will display 'YES' or 'NO' depending on values found (if a tree has been treated before the date given in argument say YES else NO).

Here's my function:

CREATE OR REPLACE FUNCTION tree_care(care_date DATE)
RETURNS TABLE(name VARCHAR(32), type VARCHAR(32), treated TEXT) AS
$$
BEGIN
RETURN QUERY
SELECT tree.name,
       tree.type,
       IF EXISTS (SELECT * FROM treatment
                  JOIN tree ON tree.name = treatment.tree_name
                  WHERE treatment.date < care_date) THEN
            'YES'::text
       ELSE
            'NO'::text
       END IF
FROM tree;
END;
$$

And I get the following error:

ERROR:  syntax error at or near "EXISTS"
LINE 8:            IF EXISTS (SELECT * FROM treatment

How does one implement an IF statement inside a SELECT?

PS: Using postgresql 9.4

Aucun commentaire:

Enregistrer un commentaire