CODEWITHSUNDEEP
sqlpostgresql
rahman
rahman

Reputation: 4948

ERROR: syntax error at or near "IF"

I have added an IF clause to my function:

CREATE OR REPLACE FUNCTION get_path_set(IN pathset_id_in character,OUT id character,OUT  pathset_id character,OUT utility double precision)
RETURNS SETOF record AS
$BODY$
BEGIN
    IF EXISTS (SELECT 1 FROM "A" WHERE "ID" = $1)  THEN 
        SELECT "ID", "PATHSET_ID", "UTILITY"
        FROM "B"
        where "PATHSET_ID" = $1;
    END IF;
END;
$BODY$

  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

And it throws an error:

ERROR:  syntax error at or near "IF"
LINE 8:  IF EXISTS (SELECT 1 FROM "A" ...

Am I missing something?

thanks

Upvotes: 0

Views: 1586

Answers (1)

Bohemian
Bohemian

Reputation: 424993

When the language is SQL, it needs to be a regular query, so convert the logic to plain SQL by moving the IF condition inside the WHERE clause:

SELECT "ID", "PATHSET_ID", "UTILITY"
FROM "B"
WHERE "PATHSET_ID" = $1
AND EXISTS (SELECT 1 FROM "A" WHERE "ID" = $1)

Upvotes: 2

Related Questions