postgresql - 在触发器内部调用的 postgres 函数中的 select 语句

Question

我正在尝试为社交媒体应用程序/网站的后端开发一个通知系统。现在我专注于状态更新。我要做的是在与状态更新相关的 postgres 表上放置一个触发器，以便每次发布新的状态更新时，都会向我的代码发送一条通知。

到目前为止，我已经能够做到这一点。但我喜欢实现的一个额外功能是提取所有关注发布状态更新的用户的人，这样我也可以向他们发送通知，告知他们关注的人发布了新的状态更新。

当然也可以先从postgres接收到新状态更新的通知，提取发帖人的用户id，查询数据库找出哪些用户关注了他们。

p>

但我认为如果我不进行查询会更有效率，相反，每次 postgres 想要向我发送有关新状态更新的通知时，它也会进行查询以找出哪些用户正在关注状态更新的发布者，并将该信息与新状态更新的通知一起发送。

但我不知道如何在依赖于该函数参数的 postgres 函数中进行查询，然后将该查询的结果与参数一起作为通知发送。

这是我试过的:

create table example (c1 text, c2 text);

create function notif() 
   returns trigger as 
$$ 
begin 
  perform pg_notify('event',row_to_json(new)::text); 
  return new; 
end; 
$$ language plpgsql;

create trigger trig after insert 
   on example 
   for each row execute procedure notif();

然后我从我的代码中监听事件 channel 并接收插入的行。但我想根据我的 notif() 函数中的新行执行 select 语句，并将包含新行的结果发送到监听代码。

如有任何澄清，我将不胜感激

谢谢

Answer 1

是这样的吗？

CREATE FUNCTION notif()
RETURNS TRIGGER AS $$
DECLARE
    data JSONB;
    result JSONB;
BEGIN
    SELECT json_agg(tmp)  -- requires Postgres9.3+
    INTO data
    FROM (
        -- your subquery goes here, for example:
        SELECT followers.following_user_id
        FROM followers
        WHERE followers.followed_user_id = NEW.user_id
    ) tmp;

    result := json_build_object('data', data, 'row', row_to_json(NEW));
    PERFORM pg_notify('event', result::TEXT);
    RETURN NEW;
END;
$$ language plpgsql;

也来自评论:

But somehow magically using return new the row is returned within the notification.

你误解了事情。 返回和通知是两个不同的东西。

首先让我们来处理返回。对于 AFTER INSERT 触发器，返回值被完全忽略:

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null.

返回值只对 BEFORE 触发器有影响。在这种情况下，您可以在写入表之前修改(甚至阻止)该行。看到这个:https://www.postgresql.org/docs/9.2/plpgsql-trigger.html 这与通知无关。

那么通知呢？ 无论您从通知中收到什么，都是您作为第二个参数传递给 pg_notify 的内容。所有这些都有很好的记录:https://www.postgresql.org/docs/9.0/sql-notify.html