PL/pgSQL 检查一行是否存在 [英] PL/pgSQL checking if a row exists

问题描述

我正在 PL/pgSQL 中编写一个函数，我正在寻找检查一行是否存在的最简单方法.
现在我正在选择一个 integer 到一个 boolean 中，这并不真正起作用.我对 PL/pgSQL 还没有足够的经验，不知道这样做的最佳方法.

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.

这是我的部分功能:

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;

推荐答案

更简单、更短、更快: EXISTS.

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

查询计划器可以在找到的第一行停止 - 与 count() 相反，它会扫描所有(匹配)行.与大桌子不同.对于唯一列上的条件，差异很小:只有一行符合条件，并且有一个索引可以快速查找.

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.

^{改进了 @a_horse_with_no_name 在评论中.}

^{Improved with input from @a_horse_with_no_name in the comments.}

你可以只使用一个空的 SELECT 列表:

You can just use an empty SELECT list:

IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN ...

SELECT 列表对EXISTS 的结果没有影响.只有至少存在一个符合条件的行才重要.

The SELECT list has no influence on the result of EXISTS. Only the existence of at least one qualifying row matters.

这篇关于PL/pgSQL 检查一行是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！