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 DECLAREd 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".