如何检查 PostgreSQL 存储过程中是否存在一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12831667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a row exists in a PostgreSQL stored procedure?
提问by ams
I writing a stored procedure in postgres where I need to check if a row exists then act accordingly. something along the line.
我在 postgres 中编写了一个存储过程，我需要检查一行是否存在，然后采取相应的行动。沿线的东西。
IF SELECT * FROM foo WHERE x = 'abc' AND y = 'xyz' THEN
-- do something here
ELSE
-- do something else
END;
I have googled a bit but got no good hits.
我用谷歌搜索了一下，但没有很好的点击。
回答by Erwin Brandstetter
回答by Craig Ringer
Use PERFORMand the FOUNDautomatic variable:
PERFORM * FROM foo WHERE x = 'abc' AND y = 'xyz';
IF FOUND THEN
....
END IF;
This will succeed if one or morerows is returned. If you want to constrain the result to exactly one row use GET DIAGNOSTICSto get the row count, or use SELECT INTOto 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 STRICTto require that exactly one rowbe obtained and stored into the target variable.
如果返回一行或多行，这将成功。如果要将结果限制为恰好一行，请使用GET DIAGNOSTICS来获取行数，或者使用将行SELECT INTO存储count(...)到DECLAREd 变量中，然后进行测试。如果没有得到结果是错误的，那么使用SELECT INTO STRICT来要求只获取一行并将其存储到目标变量中。
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".
执行此类操作时请注意并发问题。如果您正在尝试编写一个 upsert/merge 函数，这种方法将不起作用。请参阅“为什么 upsert 如此复杂”。
