PostgreSQL 触发器不返回任何内容

发布于 2024-12-12 14:44:33 字数 216 浏览 7 评论 0原文

我在创建时有一个 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

朕就是辣么酷 2024-12-19 14:44:34

你的问题留下了解释的空间。按照我的理解,您希望 INSERT 命令的 RETURNING 子句返回序列生成的主键的值。

还有其他方法可以实现这一目标。就像使用 nextval()预先从序列中获取下一个 id 并插入拼写出 id 的行。
currval() / lastval() 获取当前会话中序列/任何序列的最近获取的值。更多相关答案:
PostgreSQL 序列的下一个值?

您还可以使用RULE ... INSTEAD .. 为此目的。

但是,回答您的问题 - 如果这实际上是您的问题:可以通过使用两个触发器来完成。一个BEFORE,一个AFTER INSERT
两者都在每个定义的一个事务中触发,因此第一个表中的幻像行对任何人都不可见(触发器除外)。

演示:

CREATE TABLE x (
  id serial PRIMARY KEY  -- note the serial col.
 ,name text
);

CREATE TABLE y (
  id integer PRIMARY KEY
 ,name text
);


CREATE OR REPLACE FUNCTION trg_x_insbef()
  RETURNS trigger AS
$func$
BEGIN
  INSERT INTO y SELECT (NEW).*;  -- write to other table
  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insbef
  BEFORE INSERT ON x
  FOR EACH ROW EXECUTE PROCEDURE trg_x_insbef();


CREATE OR REPLACE FUNCTION trg_x_insaft()
  RETURNS trigger AS
$func$
BEGIN
  DELETE FROM x WHERE id = NEW.id; -- delete row again.
  RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft
  AFTER INSERT ON x
  FOR EACH ROW EXECUTE PROCEDURE trg_x_insaft();

在 psql 中调用:

db=# INSERT INTO x (name) values('phantom') RETURNING id;
 id
----
  1
(1 row)

INSERT 0 1

db=# SELECT * FROM x;
 id | name
----+------
(0 rows)


db=# SELECT * FROM y;
 id |  name
----+---------
  1 | phantom
(1 row)

Your question leaves room for interpretation. The way I understand it, you want the RETURNING clause of the INSERT 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 next id from the sequence beforehand and insert the row with the id 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, one AFTER 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:

CREATE TABLE x (
  id serial PRIMARY KEY  -- note the serial col.
 ,name text
);

CREATE TABLE y (
  id integer PRIMARY KEY
 ,name text
);


CREATE OR REPLACE FUNCTION trg_x_insbef()
  RETURNS trigger AS
$func$
BEGIN
  INSERT INTO y SELECT (NEW).*;  -- write to other table
  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insbef
  BEFORE INSERT ON x
  FOR EACH ROW EXECUTE PROCEDURE trg_x_insbef();


CREATE OR REPLACE FUNCTION trg_x_insaft()
  RETURNS trigger AS
$func$
BEGIN
  DELETE FROM x WHERE id = NEW.id; -- delete row again.
  RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft
  AFTER INSERT ON x
  FOR EACH ROW EXECUTE PROCEDURE trg_x_insaft();

Call in psql:

db=# INSERT INTO x (name) values('phantom') RETURNING id;
 id
----
  1
(1 row)

INSERT 0 1

db=# SELECT * FROM x;
 id | name
----+------
(0 rows)


db=# SELECT * FROM y;
 id |  name
----+---------
  1 | phantom
(1 row)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文