INSERT ... RETURNING ..当BEFORE触发器取消语句时出现空 [英] INSERT ...RETURNING .. comes up empty when BEFORE trigger cancels statement

问题描述

我有一个PostgreSQL插入触发器之前创建，基本上重定向插入到子表。一旦我插入记录，我想ABORT请求，以避免重复的数据（通过不插入到父表），所以我在触发器中使用返回NULL。问题是，我需要返回记录，所以我可以得到的ID。如果我返回NULL，我得到NULL。

I have a PostgreSQL before insert trigger on create that basically redirects inserts into sub-tables. Once I insert the record, I want to ABORT the request as to avoid duplicate data (by not inserting into parent table) so I am using return NULL in the trigger. The problem is that I need the record to be returned so I can get the ID. If I return NULL, I get NULL.

上面的问题在下面讨论链接：
PostgreSQL触发器不返回任何东西

The above problem is discussed at below link: PostgreSQL trigger not returning anything

（通过不返回null但返回新）并使用AFTER插入触发器从父表中删除它。但是我看到每秒1000次写入，这可能是性能的一个严重问题，因为删除。有任何其他方法这样做吗？

One of the answers says to insert into parent table (by not returning null but return new) and use AFTER insert trigger to delete it from parent table. But I am looking at 1000 writes per second and this may be a serious issue on performance because of the deletes.is there any other way to do this?

是有办法返回一个插入的行的id，而不插入到父表中并稍后删除它。

To be exact is there a way to return the id of a inserted row without inserting into the parent table and deleting it later.

推荐答案

您要查找的答案。正如我已经在那里暗示：

I wrote the answer you are referring to. As I already hinted over there:

您也可以使用 RULE ... INSTEAD .. 。

RULE

规则可能很棘手。我宁愿在可能的情况下使用触发器。 务必先阅读一下，然后再尝试：

RULE

Rules can be tricky. I'd rather use triggers where possible. Be sure to read a bit, before you try this:

CREATE OR REPLACE RULE tbl_ins AS
ON INSERT TO tbl
DO INSTEAD
INSERT INTO tbl2 (col1, col2, ...)  -- just do mention columns where ...
VALUES (NEW.col1, NEW.col2, ...)    -- ... you want to insert column defaults
RETURNING tbl2.*

这将返回 tbl2 ，同时避免幻影行。 但，每个文档 CREATE RULE ：

That would return values from tbl2 while avoiding phantom rows. However, per documentation on CREATE RULE:

INSERT ， UPDATE 或 DELETE ，可以添加
RETURNING 子句，用于发出视图的列。如果规则由
触发，则此子句将
用于计算输出。$ b INSERT RETURNING ， UPDATE RETURNING 或 DELETE RETURNING 命令。
当不带 RETURNING 的命令触发规则时，规则的
RETURNING 被忽略。 目前的实现允许
无条件 INSTEAD 规则包含 RETURNING ; / p>


In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING clause that emits the view's columns. This clause will be used to compute the outputs if the rule is triggered by an INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING command respectively. When the rule is triggered by a command without RETURNING, the rule's RETURNING clause will be ignored. The current implementation allows only unconditional INSTEAD rules to contain RETURNING;

大胆强调我。

由于您提到子表，我认为你需要条件来分发插入...

Bold emphasis mine.
Since you mention sub-tables, I take it you'd need conditions to distribute the inserts ...

如果您使用触发器 FOR EACH ROW 操作，您可以轻松地从序列中提取适当的值 currval（） / lastval（）。棘手的部分是从触发器函数返回这些值。我只能想到写一个临时表。需要一些思考何时创建和什么时候删除一个...

If you operate with a trigger FOR EACH ROW you can easily fetch appropriate values from sequences with currval() / lastval(). The tricky part is to return those values from a trigger function. I can only think of writing to a temporary table. Needs some thinking when to create and when to drop that one ...

我可能会重新思考整个方法并重定向数据到多个 INSERT 语句到实际目标表...

I would probably rethink the whole approach and redirect the data to multiple INSERT statements to actual target tables ...

这篇关于INSERT ... RETURNING ..当BEFORE触发器取消语句时出现空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！