PostgreSQL 触发器不返回任何内容
我在创建时有一个 PostgreSQL 触发器,它基本上将插入重定向到子表中。插入记录后,我想中止请求以避免重复数据。 (据我所知)执行此操作的唯一方法是在触发器中返回 NULL。问题是我需要返回记录才能获取 ID。如果我返回NULL
,我会得到...NULL
。
知道如何让触发器中止操作,同时仍返回 NULL
以外的内容吗?
I have a PostgreSQL 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. The only way (that I know of) to do this is to 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
.
Any idea how I can have a trigger abort an operation while still returning something other than NULL
?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
你的问题留下了解释的空间。按照我的理解,您希望 INSERT 命令的 RETURNING 子句返回序列生成的主键的值。
还有其他方法可以实现这一目标。就像使用
nextval()
预先从序列中获取下一个id
并插入拼写出id
的行。或
currval()
/lastval()
获取当前会话中序列/任何序列的最近获取的值。更多相关答案:PostgreSQL 序列的下一个值?
您还可以使用
RULE ... INSTEAD ..
为此目的。但是,回答您的问题 - 如果这实际上是您的问题:可以通过使用两个触发器来完成。一个
BEFORE
,一个AFTER INSERT
。两者都在每个定义的一个事务中触发,因此第一个表中的幻像行对任何人都不可见(触发器除外)。
演示:
在 psql 中调用:
Your question leaves room for interpretation. The way I understand it, you want the
RETURNING
clause of theINSERT
command to return the value of the primary key generated by a sequence.There are other ways to achieve this. Like using
nextval()
to get the nextid
from the sequence beforehand and insert the row with theid
spelled out.OR
currval()
/lastval()
to get the most recently obtained value for for a sequence / any sequence in the current session. More in this related answer:PostgreSQL next value of the sequences?
You could also use a
RULE ... INSTEAD ..
for this purpose.But, to answer your question - if that is, in fact, your question: it can be done by using two triggers. One
BEFORE
, oneAFTER INSERT
.Both are fired in one transaction per definition, so the phantom row in your first table is never visible to anybody (except the triggers).
Demo:
Call in psql: