IF NOT EXISTS within a RETURN QUERY statement

Question

I have a PostgreSQL function which takes in a list of strings, matches such strings within a Word table and returns them accordingly. Word is a table consisting of a string word as a primary ID and attributed features to that string.

This is the working version of the function.

CREATE OR REPLACE FUNCTION equals_words(words Text[]) RETURNS SETOF "Word" AS $BODY$
DECLARE
  w Text;
BEGIN
  FOREACH w IN ARRAY $1
  LOOP
    RETURN QUERY SELECT * FROM "Word" WHERE word = w
  END LOOP;
  RETURN;
END; $BODY$ LANGUAGE plpgsql STABLE STRICT;

I would like the the statement within the loop to check whether the query returned nothing, and if so, call another function longest_prefix(w TEXT) which attempts to match w with its longest prefix. This is what I have so far.

CREATE OR REPLACE FUNCTION equals_words(words Text[]) RETURNS SETOF "Word" AS $BODY$
DECLARE
  w Text;
BEGIN
  FOREACH w IN ARRAY $1
  LOOP
    RETURN QUERY 
        IF NOT EXISTS(SELECT * FROM "Word" WHERE word = w)
        BEGIN
        SELECT * FROM longest_prefix(w); 
        END
  END LOOP;
  RETURN;
END; $BODY$ LANGUAGE plpgsql STABLE STRICT;

This does not work, giving me the below error:

ERROR:  syntax error at or near "IF"
LINE 8:   IF NOT EXISTS(SELECT * FROM "Word" WHERE word = w)

I suspect NOT EXISTS can not be used within a RETURN QUERY statement but I am not too sure. Can someone suggest an alternative if so?

Answer 1

You can use a CTE and UNION ALL to put this all in one query:

WITH cte AS (
      SELECT *
      FROM "Word" w
      WHERE w.word = w
     )
SELECT cte.*
FROM cte
UNION ALL
SELECT *
FROM longest_prefix(w)
WHERE NOT EXISTS (SELECT 1 FROM cte);