PostgreSQL中如何通过LISTEN在收到通知后触发内部函数？

PostgreSQL原生并不支持LISTEN <channel> THEN PERFORM ...这种直接绑定触发的语法，但可以通过后台循环监听+通知处理的方式，在数据库内部实现收到通知时自动执行函数的需求。以下是具体实现步骤和示例：

1. 创建通知处理函数 #

先定义一个你希望在收到通知时触发的函数，用于处理业务逻辑、记录日志等：

CREATE OR REPLACE FUNCTION handle_notification(p_payload text)
RETURNS void AS $$
BEGIN
  -- 替换为你的实际业务逻辑
  RAISE NOTICE '收到频道通知，内容：%', p_payload;
  -- 示例：调用其他业务函数
  -- PERFORM your_business_function(p_payload);
END;
$$ LANGUAGE plpgsql;

2. 创建监听循环函数 #

编写一个PL/pgSQL函数，通过无限循环监听指定频道，一旦收到通知就调用处理函数：

CREATE OR REPLACE FUNCTION listen_for_notifications(p_channel text)
RETURNS void AS $$
DECLARE
  v_notify record;
BEGIN
  -- 动态监听指定频道（%I用于处理标识符转义）
  EXECUTE format('LISTEN %I', p_channel);
  
  -- 持续循环等待通知
  LOOP
    -- 获取新通知，无通知时返回NULL
    SELECT * FROM pg_get_notify() INTO v_notify;
    
    IF v_notify IS NOT NULL THEN
      -- 触发处理函数，传入通知内容
      PERFORM handle_notification(v_notify.payload);
    END IF;
    
    -- 休眠1秒，避免过度占用CPU
    PERFORM pg_sleep(1);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

3. 启动监听进程 #

监听函数是会话级的，必须保持会话持续运行才能维持监听，以下是几种常用启动方式：

方式一：使用pg_background扩展（推荐后台运行） #

先安装pg_background扩展（需数据库超级权限）：

CREATE EXTENSION IF NOT EXISTS pg_background;

后台启动监听：

SELECT pg_background_launch('SELECT listen_for_notifications(''my_channel'');');

方式二：在psql中后台执行 #

通过命令行启动psql并后台运行监听函数：

psql -d 你的数据库名 -c "SELECT listen_for_notifications('my_channel');" &

方式三：使用dblink保持会话 #

先安装dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

通过dblink创建持久会话并启动监听：

SELECT dblink_connect('dbname=你的数据库名');
SELECT dblink_exec('SELECT listen_for_notifications(''my_channel'');');

4. 测试通知触发 #

发送一条测试通知，验证处理函数是否被触发：

SELECT pg_notify('my_channel', '这是一条测试通知');

此时可以在监听会话的日志中看到收到频道通知，内容：这是一条测试通知的输出。

注意事项 #

• 监听会话一旦断开，监听就会停止，需要用systemd、容器等方式确保进程持续运行。
• 可修改监听函数支持多频道监听，或启动多个进程处理不同频道。
• 调整pg_sleep时长可平衡响应速度与CPU占用。

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