How to check if a row exists in a PostgreSQL stored procedure?
Or even simpler with EXISTS
:
IF EXISTS (SELECT FROM foo WHERE x = 'abc' AND y = 'xyz') THEN
...
END IF;
See:
- PL/pgSQL checking if a row exists
Use PERFORM
and the FOUND
automatic variable:
PERFORM * FROM foo WHERE x = 'abc' AND y = 'xyz';
IF FOUND THEN
....
END IF;
This will succeed if one or more rows is returned. If you want to constrain the result to exactly one row use GET DIAGNOSTICS
to get the row count, or use SELECT INTO
to store the count(...)
of the rows into a DECLARE
d variable you then test. If it's an error to get no results, use SELECT INTO STRICT
to require that exactly one row be obtained and stored into the target variable.
Beware of concurrency issues when doing anything like this. If you're attempting to write an upsert/merge function this approach will not work. See "why is upsert so complicated".