在更新后使用更新中的数据来呼叫Notify之后，请创建Postgres语句触发

发布于 2025-01-23 06:52:23 字数 1035 浏览 0 评论 0原文

我想创建一个触发器，每当insert在表中的语句上插入语句，使用插入的数据来调用pg_notify：

CREATE OR REPLACE FUNCTION notify_updates() RETURNS TRIGGER AS $$
   BEGIN
    PERFORM pg_notify(NEW.aggregate_type, NEW.aggregate_id::text);
    RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER model_notification
 AFTER INSERT ON events
 FOR EACH STATEMENT
 EXECUTE PROCEDURE notify_updates()

gencregate_type和gentregate_id是事件表的列。 gengregate_type的值是事先知道的，并且可以在相关频道上提前调用listing。

我得到的错误说“频道名称不能为空”。我想这是因为触发器为每个语句而不是每行都运行。
有没有办法为语句触发器做这项工作吗？我认为有趣的部分是可能会插入几行，而Postgres不知道应该选择哪种价值？

这个问题类似于使用pg_notify trigger中的行数据频道名称？，但是我想在每个语句上运行触发器，而不是每行，因为在我的特殊情况下，gengregate_type 和gengregate_id将是每个插件上的所有行都相同。

原文

I'd like to create a trigger that runs whenever an INSERT statement on a table succeeded, using the data from the insert to call pg_notify:

CREATE OR REPLACE FUNCTION notify_updates() RETURNS TRIGGER AS $
   BEGIN
    PERFORM pg_notify(NEW.aggregate_type, NEW.aggregate_id::text);
    RETURN NEW;
   END;
$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER model_notification
 AFTER INSERT ON events
 FOR EACH STATEMENT
 EXECUTE PROCEDURE notify_updates()

aggregate_type and aggregate_id are columns of the events table. the values for aggregate_type are known in advance and it's possible to call LISTEN on the relevant channels in advance.

The error I get says "channel name cannot be empty". I guess it's because the trigger runs for each statement and not for each row.
Is there a way to make this work for statement triggers? I think the interesting part is that there might be a couple of rows that were inserted and postgres doesn't know which value it should choose?

This question is similar to Using row data in pg_notify trigger as channel name?, but I want to have the trigger run on each statement instead of each row because in my particular case the values for aggregate_type and aggregate_id will be the same for all rows on each insert.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

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

鹤仙姿 2025-01-30 06:52:24

您需要一个过渡表：

CREATE OR REPLACE FUNCTION notify_updates() RETURNS TRIGGER AS $
   BEGIN
    PERFORM pg_notify(aggregate_type, aggregate_id::text)
      FROM new_table -- transition table
      LIMIT 1;       -- !!
    RETURN NEW;
   END;
$ LANGUAGE plpgsql;

CREATE TRIGGER model_notification
 AFTER INSERT ON events
 REFERENCING NEW TABLE AS new_table -- define transition table name
 FOR EACH STATEMENT
 EXECUTE PROCEDURE notify_updates();

阅读文档中有关功能的更多信息：

create trigger

“另外触发器将从几行构建一行。

You need a transition table:

CREATE OR REPLACE FUNCTION notify_updates() RETURNS TRIGGER AS $
   BEGIN
    PERFORM pg_notify(aggregate_type, aggregate_id::text)
      FROM new_table -- transition table
      LIMIT 1;       -- !!
    RETURN NEW;
   END;
$ LANGUAGE plpgsql;

CREATE TRIGGER model_notification
 AFTER INSERT ON events
 REFERENCING NEW TABLE AS new_table -- define transition table name
 FOR EACH STATEMENT
 EXECUTE PROCEDURE notify_updates();

Read more about the feature in the documentation:

Transition tables were introduced in Postgres 10. Read also Trigger that will build one row from several rows.

回复收藏 0 原文

~没有更多了~