How to check if a row exists in a PostgreSQL stored procedure?

Question

I writing a stored procedure in postgres where I need to check if a row exists then act accordingly. something along the line.

IF SELECT * FROM foo WHERE x = 'abc' AND y = 'xyz' THEN
  -- do something here
ELSE 
  -- do something else
END;

I have googled a bit but got no good hits.

Solution 1

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

Solution 2

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

Comments

[ams]

IS FOUND some implicit variable ? tried to find where it was mentioned in the docs but could not, its too generic a term while searching.

[Craig Ringer]

@ams from the linked page above, "Also, the special variable FOUND is set to true if the query produced at least one row, or false if it produced no rows (see Section 39.5.5)." . See 39.5.5. Obtaining the Result Status; it's also mentioned elsewhere throughout the "basic statements" page on PL/PgSQL.