时间戳

首页 > 解决方案 > 在触发器内调用的 postgres 函数中的 select 语句

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

问题描述

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

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

当然可以这样实现，首先从 postgres 接收到新状态更新的通知，提取发布者的用户 id，然后查询数据库以找出哪些用户关注了他们。

但是我认为如果我不进行查询会更有效，相反，每次 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();

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

我会很感激任何澄清

谢谢

标签： postgresqlplpgsqldatabase-trigger

解决方案

像这样的东西？

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;

同样来自评论：

但是以某种方式神奇地使用return new该行在通知中返回。

你误解了一些事情。退货和通知是两个不同的东西。

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

AFTER 触发的行级触发器或 BEFORE 或 AFTER 触发的语句级触发器的返回值总是被忽略；它也可能为空。

返回值仅对 BEFORE 触发器有意义。在这种情况下，您可以在写入表之前修改（甚至阻止）该行。请参阅：https ://www.postgresql.org/docs/9.2/plpgsql-trigger.html 这与通知无关。

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

推荐阅读