postgresql 创建listen notify .net core6.0监听连接

背景：数据库某张表定时有第三方更新到实时表里，收到数据更新以后，WEBSOCKET发送前端

-- 创建一个发送通知的函数
CREATE OR REPLACE FUNCTION notify_event(event_name text) RETURNS void AS $$
BEGIN
  PERFORM pg_notify(event_name, '');
END;
$$ LANGUAGE plpgsql;

-- 创建一个触发器，在数据变化时调用通知函数
CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$
BEGIN
  PERFORM notify_event('data_changed');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 

-- 在需要监听的表上创建触发器
CREATE TRIGGER data_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON river_area
FOR EACH ROW
EXECUTE FUNCTION notify_trigger();
 

.net core创建一个后台运行服务

using System;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Npgsql;

namespace Pipe.BLL
{
    public class TableUpdateListenerService : BackgroundService
    {
        private readonly IServiceProvider _services;

        public TableUpdateListenerService(IServiceProvider services)
        {
            _services = services;
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            using var scope = _services.CreateScope();
            var serviceProvider = scope.ServiceProvider;

            var connectionString = serviceProvider.GetRequiredService<IConfiguration>().GetConnectionString("LocalNpgsqlConnection");

            using var conn = new NpgsqlConnection(connectionString);
            await conn.OpenAsync(stoppingToken);

            using var cmd = new NpgsqlCommand("listen data_changed", conn);
            cmd.ExecuteNonQuery();

            conn.Notification += (sender, e) =>
            {
                Console.WriteLine($"Received table update notification: {e.Payload}");
                // 在这里执行相应的操作，处理接收到的通知
            };

            while (!stoppingToken.IsCancellationRequested)
            {
                using var transaction = conn.BeginTransaction();
                await transaction.CommitAsync();
                await Task.Delay(TimeSpan.FromSeconds(1), stoppingToken); // 可以选择适当的延迟时间
            }
        
        }
    }
}

startup.cs注册下服务   services.AddHostedService<TableUpdateListenerService>();

psql 发送测试。

UPDATE "public"."river_area" SET "objectid" = 5 WHERE "gid" = 3;

NOTIFY data_changed, 'Your notification payload here'; 测试通过