PostgreSQL触发器报错：pg_notify子查询返回多行及notify使用问题

针对pg_notify的"more than one row returned by a subquery used as an expression"错误 #

错误根源 #

1. 触发器调用方式错误：在PL/pgSQL中，执行pg_notify这类无返回值的函数时，不能用SELECT语句，否则会触发不必要的结果集处理逻辑，引发错误。
2. INSERT子查询返回多行：你的INSERT语句中(SELECT id from network WHERE name='ZZ')如果返回多个ID，会导致INSERT操作失败，进而触发触发器报错。

修复方案 #

• 修正触发器中的pg_notify调用：用PERFORM替代SELECT，这是PL/pgSQL中执行无返回值函数的标准写法：

CREATE OR REPLACE FUNCTION notify_link_insert() RETURNS trigger LANGUAGE plpgsql AS $$
 begin
  PERFORM pg_notify('link_topic_manager', to_jsonb(new)::text);
  return new;
end;
$$;

CREATE TRIGGER link_manager_trigger AFTER INSERT ON link FOR EACH ROW EXECUTE PROCEDURE notify_link_insert();

• 确保INSERT子查询仅返回单行：要么给子查询加LIMIT 1，要么给network.name字段添加唯一约束，避免多条匹配：

INSERT INTO link (network_id, sender_id, target_id, protocol) 
VALUES ((SELECT id from network WHERE name='ZZ' LIMIT 1), 'zz44', 'zzz', 'z123');

针对NOTIFY无法传递新行数据的问题 #

错误根源 #

NOTIFY的消息参数必须是文本类型，直接传入to_jsonb(new)会因为类型不匹配，导致新行的JSON数据无法正确传递，而非触发器不返回新行。

修复方案 #

将新行的JSON数据转为文本后再传入NOTIFY，可以用EXECUTE format来安全拼接语句：

CREATE OR REPLACE FUNCTION notify_link_insert() RETURNS trigger LANGUAGE plpgsql AS $$
 begin
  EXECUTE format('NOTIFY link_topic_manager, %L', to_jsonb(new)::text);
  return new;
end;
$$;

更推荐用pg_notify：相比原生NOTIFY，pg_notify函数的语法更简洁，不需要手动拼接SQL，更不容易出错，就是前面修复方案里的写法。

内容的提问来源于stack exchange，提问作者Steven Garcia