仅当不存在行时才在PL/pgSQL中运行SQL语句 [英] Run SQL statements in PL/pgSQL only if a row doesn't exist

问题描述

我想在Postgres 9.6的PL/pgSQL函数中做类似的事情:

I want to do something like this in a PL/pgSQL function in Postgres 9.6:

INSERT INTO table1 (id, value) VALUES (1, 'a') ON CONFLICT DO NOTHING;
--- If the above statement didn't insert a new row
---   because id of 1 already existed, 
---   then run the following statements

INSERT INTO table2 (table1_id, value) VALUES (1, 'a');
UPDATE table3 set (table1_id, time) = (1, now());

但是，我不知道如何确定第一个INSERT是否实际插入了新行，或者ON CONFLICT DO NOTHING是否已完成.

However, I don't know how to determine whether the first INSERT actually inserted a new row, or whether the the ON CONFLICT DO NOTHING was done.

我可以在函数的开头执行SELECT，以查看在运行所有SQL语句之前table1中是否存在id为1的记录，但这会导致竞争状况. /p>

I could do a SELECT at the beginning of the function to see whether a record with id of 1 exists in table1 before running all the SQL statements, but this would lead to race conditions I think.

推荐答案

对于plpgsql函数，请使用

For a plpgsql function, use the special variable FOUND:

CREATE FUNCTION foo(int, text)
 RETURNS void AS
$$
BEGIN
   INSERT INTO table1 (id, value) VALUES ($1, $2) ON CONFLICT DO NOTHING;

   IF NOT FOUND THEN
      INSERT INTO table2 (table1_id, value) VALUES ($1, $2);
      UPDATE table3 set (table1_id, time) = ($1, now())
      WHERE  ????;  -- you surely don't want to update all rows in table3
   END IF;
END
$$

致电:

SELECT foo(1, 'a');

如果INSERT实际上没有插入任何行，则

FOUND设置为 false .

FOUND is set to false if the INSERT does not actually insert any rows.

有关的手册ON CONFLICT条款:

ON CONFLICT DO NOTHING只是避免插入一行作为其 替代行动.

ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.

关于获取的手册结果状态

如果至少一个

UPDATE，INSERT和DELETE语句将FOUND设置为true 行受到影响，如果没有行受到影响则为false.

UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

需要明确的是，如果table1 中的行已经存在，那么它将运行后面的语句，因此不会插入新行. (就像您要求的，但与您的问题标题相反.)

To be clear, this runs the later statements if a row in table1 does already exist, so the new row is not inserted. (Like you requested, but contrary to your question title.)

如果您只想检查是否存在行:

If you just want to check whether a row exists:

• PostgreSQL IF语句

如果同一事务中的后续命令 依赖于table1中的现有行(例如，带有FK)，则需要锁定它以防止并发事务删除或更新它同时.一种方法是:使用DO UPDATE代替DO NOTHING，但实际上不更新 .该行仍处于锁定状态:

If subsequent commands in the same transaction depend on the existing row in table1 (with a FK for instance), you'll want to lock it to defend against concurrent transactions deleting or updating it in the meantime. One way to do this: instead of DO NOTHING use DO UPDATE, but do not actually update the row. The row is still locked:

INSERT INTO table1 AS t (id, value)
VALUES ($1, $2)
ON     CONFLICT (id) DO UPDATE  -- specify unique column(s) or constraint / index
SET    id = t.id WHERE FALSE;   -- never executed, but locks the row

很明显，如果您可以排除可能以冲突方式删除或更新同一行的并发事务，则该问题不存在.

Obviously, if you can rule out concurrent transactions that might delete or update the same row in a conflicting manner, then the problem does not exist.

详细说明:

• 如何包括排除了从INSERT ... ON CONFLICT返回的行中的行
• 是SELECT还是在容易出现竞争状况的函数中插入

• How to include excluded rows in RETURNING from INSERT ... ON CONFLICT
• Is SELECT or INSERT in a function prone to race conditions?

这篇关于仅当不存在行时才在PL/pgSQL中运行SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！