仅当不存在行时才在PL / pgSQL中运行SQL语句

一尘不染

仅当不存在行时才在PL / pgSQL中运行SQL语句

sql

我想在Postgres 9.6的PL / pgSQL函数中做这样的事情：

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已完成。

我可以SELECT在函数的开头执行a ，以查看在运行所有SQL语句之前是否存在记录id为1的记录table1，但是我认为这会导致争用情况。

阅读 168

2021-03-17

共1个答案

一尘不染

对于plpgsql函数，请使用特殊变量
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');

FOUND如果并未实际插入任何行，则将其设置为 falseINSERT。

关于 _ON CONFLICT
条款_的手册：

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

关于 获取结果状态 的手册 __

UPDATE，INSERT和DELETE语句FOUND如果至少影响一行，则设置为true；如果不影响任何行，则设置为false。

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

如果只想检查是否存在一行：

PostgreSQL IF语句

比赛条件？

如果同一事务中的后续命令依赖
于其中的现有行table1（例如，带有FK），则您将需要对其进行锁定，以防止并发事务同时删除或更新它。一种执行此方法的方法：代替DO NOTHINGuse DO UPDATE，但实际上不更新该行。该行仍处于锁定状态：

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

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

详细说明：

2021-03-17