PostgreSQL能否创建监听函数捕获Notify事件以写入同库另一表？

Great question! Let's break this down clearly—PostgreSQL's LISTEN/NOTIFY system is client-side by design, so you can't create a server-side function that "listens" for notifications directly. But there are two solid ways to achieve your goal of writing notified data to the events table:

1. Simplest Solution: Write to events Directly in Your Existing Trigger #

If you don't need strict decoupling between the mytable insert and the events write, just extend your existing mynotify() function to handle the insert alongside the notification. This avoids the need for any listening logic entirely:

CREATE OR REPLACE FUNCTION public.mynotify() RETURNS trigger AS $BODY$
BEGIN
    -- Insert the new row's data into events (adjust column name to match your schema)
    INSERT INTO public.events (data)
    VALUES (row_to_json(NEW)::text);
    
    -- Keep sending the notification if you still need other clients to listen
    PERFORM pg_notify('myEvent', row_to_json(NEW)::text);
    
    RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

This is efficient, transaction-safe, and requires no extra tools. The insert into events will roll back automatically if the original mytable insert fails, keeping your data consistent.

2. Decoupled Solution: Use an External Listener Program #

If you need to keep the mytable insert and events write separate (e.g., to add other listeners later, or avoid tying the two operations together), you'll need a small external program that maintains a persistent connection to PostgreSQL, listens for myEvent notifications, and writes to events when it receives them.

Here's a quick example using Python and psycopg2 (you could also use Node.js, Go, or any language with a PostgreSQL driver):

import psycopg2
from psycopg2 import sql

def run_listener():
    # Update these connection details to match your database
    conn = psycopg2.connect(
        dbname="your_database",
        user="your_user",
        password="your_password",
        host="localhost"
    )
    # Auto-commit is required for LISTEN/NOTIFY to work properly
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    cur = conn.cursor()
    
    # Start listening for the myEvent channel
    cur.execute("LISTEN myEvent;")
    print("Listening for 'myEvent' notifications...")

    while True:
        # Check for new notifications
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            print(f"Received notification: {notify.payload}")
            
            # Insert the payload into the events table
            cur.execute(
                sql.SQL("INSERT INTO public.events (data) VALUES (%s);"),
                (notify.payload,)
            )
            conn.commit()

if __name__ == "__main__":
    try:
        run_listener()
    except KeyboardInterrupt:
        print("Listener stopped.")

You can run this script as a background service (e.g., using systemd on Linux, or a Windows service) to keep it running 24/7. This approach keeps your database logic clean and lets you add more processing to the notification data later if needed.

Why a Server-Side Listening Function Isn't Possible #

PostgreSQL's LISTEN command requires an active, persistent client connection. Server-side functions (like triggers or stored procs) run within a short-lived transaction context—once the function finishes, the connection is closed, and the LISTEN state is lost. There's no built-in way to maintain a permanent LISTEN state directly within the database server itself.

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