时间戳

首页 > 解决方案 > 触发函数内部数组的for循环问题

postgresql - 触发函数内部数组的for循环问题

问题描述

我试图在桌子上放一个触发器。此表有一个正文列，其中包含文本类型。此列中的文本可以包含 @{username} 提及，我需要提取它们并将它们写入数据库并通知我的应用程序。

下面的代码有效，但我认为在 foreach 块中并不理想，我正在做一个额外的选择来获取与用户名关联的 user_id。但问题是，我无法弄清楚如何将提及的 json 作为数组进行 foreach。我已经尝试了很多东西，但我只是一个接一个地出错，坦率地说，我现在很头疼！

对我来说也很奇怪，我必须拥有用户名数组才能提取用户名，然后将 id,username,firebase_id 放在另一个数组中，但我无法同时选择 parse_tokens 的结果和用户表中的 id,firebase_id到单个数组。

有任何想法吗？欢迎任何其他建议。谢谢

create or replace function notif() returns trigger as $$
  declare
    usernames text[];
    mentions json;
    mu text;
  begin
    select parse_tokens(new.body, '@') into usernames;
    select json_agg(tmp)
      from (
        select id, username, firebase_id
        into mentions
        from users
        where username=any(usernames)
      ) tmp;

    perform pg_notify('status', mentions::text);

    foreach mu in array usernames loop
      insert into mentions (status_id, from_user_id, to_user_id) values
        (new.id, new.user_id, (select id from users where username=mu));
    end loop;

    return new;
end; $$ language plpgsql;

标签： postgresqlfunctionloopstriggers

解决方案

好的，我解决了这个问题。这正是我想要的：

create type mention_info as (id integer, username varchar(64), firebase_id varchar(64));

create or replace function notif() returns trigger as $$
  declare
    mentions mention_info[];
    m mention_info;
  begin
    select array_agg(tmp) into mentions from 
      (select id, username, firebase_id from users
        where username=any(parse_tokens(new.body, '@'))) tmp;

    perform pg_notify('status', mentions::text);
    foreach m in array mentions loop
      insert into mentions
        (status_id, from_user_id, to_user_id) values (new.id, new.user_id, m.id);
    end loop;

    return new;
  end;
$$ language plpgsql;

推荐阅读