[SOLVED] PL/pgSQL checking if a row exists

PL/pgSQL checking if a row exists

I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists.
Right now I'm SELECTing an integer into a boolean, which doesn't really work. I'm not experienced with PL/pgSQL enough yet to know the best way of doing this.

Here's part of my function:

DECLARE person_exists boolean;
BEGIN

person_exists := FALSE;

SELECT "person_id" INTO person_exists
  FROM "people" p
WHERE p.person_id = my_person_id
LIMIT 1;

IF person_exists THEN
  -- Do something
END IF;

END; $$ LANGUAGE plpgsql;

Update - I'm doing something like this for now:

DECLARE person_exists integer;
BEGIN

person_exists := 0;

SELECT count("person_id") INTO person_exists
  FROM "people" p
WHERE p.person_id = my_person_id
LIMIT 1;

IF person_exists < 1 THEN
  -- Do something
END IF;

Solution

Simpler, shorter, faster: EXISTS.

IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN
  -- do something
END IF;

The query planner can stop at the first row found - as opposed to count(), which scans all (qualifying) rows regardless. Makes a big difference with big tables. The difference is small for a condition on a unique column: only one row qualifies and there is an index to look it up quickly.

Only the existence of at least one qualifying row matters. The SELECT list can be empty - in fact, that's shortest and cheapest. (Some other RDBMS don't allow an empty SELECT list on principal.)