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;
In PostgreSQL, the EXISTS operator is used to test for the existence of rose in a subquery.It is generally used with correlated subqueries. If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false.
In Postgres, the IF-statement only treats the true condition. The syntax of the IF statement is as follows: IF condition THEN statement; END IF; Here, condition represents a criterion that must be satisfied to execute the statements specified within the THEN block.
PL/pgSQL (Procedural Language/PostgreSQL) is a procedural programming language supported by the PostgreSQL ORDBMS.
With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings because you don't have the whole client/server communication overhead.
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. 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.
Improved with input from @a_horse_with_no_name in the comments.
You can just use an empty SELECT
list:
IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN ...
The SELECT
list has no influence on the result of EXISTS
. Only the existence of at least one qualifying row matters.
Use count(*)
declare cnt integer; begin SELECT count(*) INTO cnt FROM people WHERE person_id = my_person_id; IF cnt > 0 THEN -- Do something END IF;
Edit (for the downvoter who didn't read the statement and others who might be doing something similar)
The solution is only effective because there is a where clause on a column (and the name of the column suggests that its the primary key - so the where clause is highly effective)
Because of that where
clause there is no need to use a LIMIT or something else to test the presence of a row that is identified by its primary key. It is an effective way to test this.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With