CREATE OR REPLACE FUNCTION test()
RETURNS boolean security definer AS
$BODY$
BEGIN
with zzz as (select foo from data)
IF EXISTS (select 1 from zzz) THEN
return true;
ELSE
return false;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ERROR: syntax error at or near "IF" LINE 10: IF EXISTS (select 1 from zzz) THEN
Where is the problem?
Simplify to a single return statement:
return exists(select foo from data);
Like Bohemian provided, you can radically simplify to:
RETURN EXISTS(SELECT foo FROM data);
However, that would suggest that foo matters, which it does not (if the column exists, else an exception is raised). EXISTS returns true if at least one row is returned, no matter the content: can even be all NULL values for that purpose. Else it returns false. Never null. Less misleading:
RETURN EXISTS(SELECT * FROM data);
RETURN EXISTS(SELECT 1 FROM data);
Postgres also accepts an empty SELECT list (my preference):
RETURN EXISTS(SELECT FROM data);
See:
Or use the syntax shorthand TABLE for the simple case:
RETURN EXISTS(TABLE data);
See:
PL/pgSQL would be overkill for the simple case, a plain SQL function or just the bare statement would do:
SELECT EXISTS(TABLE data);
To integrate the logic in a more complex PL/pgSQL function, the special variable FOUND (like Adrian commented) is often instrumental. Or GET DIAGNOSTICS ... for dynamic SQL.
See:
Finally I used:
$BODY$
BEGIN
DROP TABLE IF EXISTS mytable;
CREATE TEMP TABLE mytable AS
SELECT foo from data;
IF EXISTS (select 1 from mytable) THEN
return true;
ELSE
return false;
END IF;
END;
$BODY$