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;
Simpler, shorter, faster: EXISTS
.
IF EXISTS (SELECT 1 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 will scan all matching rows regardless. Makes a difference with big tables. Hardly matters with a condition on a unique column - only one row qualifies anyway (and there is an index to look it up quickly).
Improved with input from @a_horse_with_no_name in the comments below.
You could even use an empty SELECT
list:
IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN ...
Since the SELECT
list is not relevant to the outcome of EXISTS
. Only the existence of at least one qualifying row matters.