如何检查PostgreSQL存储过程中是否存在行？ [英] How to check if a row exists in a PostgreSQL stored procedure?

问题描述

我在postgres中编写一个存储过程，需要检查是否存在一行，然后采取相应的措施。

I writing a stored procedure in postgres where I need to check if a row exists then act accordingly. something along the line.

IF SELECT * FROM foo WHERE x = 'abc' AND y = 'xyz' THEN
  -- do something here
ELSE 
  -- do something else
END;

我已经用Google搜索了一下，但没有很好的命中率。

I have googled a bit but got no good hits.

推荐答案

使用 PERFORM 和 FOUND 自动变量：

Use PERFORM and the FOUND automatic variable:

PERFORM * FROM foo WHERE x = 'abc' AND y = 'xyz';
IF FOUND THEN
    ....
END IF;

如果返回或更多行，此操作将成功。如果要将结果限制为仅一行使用获取诊断获取行数，或使用 SELECT INTO 存储<$ c $将行中的c> count（...）放入 DECLARE d变量中，然后进行测试。如果没有结果是错误的，请使用 SELECT INTO STRICT 要求获取恰好一行并将其存储到目标变量中。

This will succeed if one or more rows is returned. If you want to constrain the result to exactly one row use GET DIAGNOSTICS to get the row count, or use SELECT INTO to store the count(...) of the rows into a DECLAREd variable you then test. If it's an error to get no results, use SELECT INTO STRICT to require that exactly one row be obtained and stored into the target variable.

执行类似操作时要注意并发问题。如果您尝试编写upsert / merge函数，则此方法不起作用。参见为什么upsert如此复杂

Beware of concurrency issues when doing anything like this. If you're attempting to write an upsert/merge function this approach will not work. See "why is upsert so complicated".

这篇关于如何检查PostgreSQL存储过程中是否存在行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！