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.

我正在PL / pgSQL中编写一个函数，我正在寻找检查行是否存在的最简单方法。现在我正在将整数选择为布尔值，这实际上并不起作用。我对PL / pgSQL还没有足够的经验知道最好的方法。

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;

2 个解决方案

#1

Simpler, shorter, faster: EXISTS.

更简单，更短，更快：EXISTS。

IF EXISTS (SELECT 1 FROM people WHERE 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).

查询计划程序可以在找到的第一行停止 - 而不是count（），它将扫描所有匹配的行。与大桌子有所不同。对于唯一列上的条件几乎不重要 - 无论如何只有一行符合条件（并且有一个索引可以快速查找）。

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

在下面的评论中改进了来自@a_horse_with_no_name的输入。

#2

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)

编辑（对于未阅读该声明的downvoter和可能正在做类似事情的其他人）

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)

该解决方案仅有效，因为列上有where子句（并且列的名称表明它是主键 - 因此where子句非常有效）

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.

由于where子句，不需要使用LIMIT或其他东西来测试由其主键标识的行的存在。这是一种有效的测试方法。

#1