PL/pgSQL checking if a row exists

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

^{Improved with @a_horse_with_no_name’s comments.}