postgresql - Postgres 通知 : notify with row id on row create/delete/update in table

Question

我想用发送 NOTIFY 的 trigger_function 创建 LISTE/NOTIFY 管道。

在我的 NOTIFY 中，我想为任何 create/delete/update 获取带有行 id 的消息，其中行在表中。

如何编写这样的通知 trigger_function ？

到目前为止，我还有下一次迁移吗？女巫创建没有行 id

的触发器

CREATE OR REPLACE FUNCTION notify_my_table_update() RETURNS TRIGGER AS $$
    BEGIN
    PERFORM pg_notify('my_table_update','');
    RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_my_table_update ON my_table;
CREATE TRIGGER trigger_my_table_update
  AFTER UPDATE OR DELETE OR INSERT OR TRUNCATE
  ON my_table
  EXECUTE PROCEDURE notify_my_table_update();

Answer 1

步骤如下:

1. 创建表my_table

CREATE TABLE my_table(
id int,
data varchar
)

1. 然后编写触发过程:

CREATE OR REPLACE FUNCTION notify_my_table_update() RETURNS TRIGGER AS $$
    DECLARE
    row RECORD;
    output TEXT;
    
    BEGIN
    -- Checking the Operation Type
    IF (TG_OP = 'DELETE') THEN
      row = OLD;
    ELSE
      row = NEW;
    END IF;
    
    -- Forming the Output as notification. You can choose you own notification.
    output = 'OPERATION = ' || TG_OP || ' and ID = ' || row.id;
    
    -- Calling the pg_notify for my_table_update event with output as payload

    PERFORM pg_notify('my_table_update',output);
    
    -- Returning null because it is an after trigger.
    RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

1. 为 INSERT/UPDATE/DELETE
在表 initial_cost_tasks 上创建 after 触发器

CREATE TRIGGER trigger_my_table_update
  AFTER INSERT OR UPDATE OR DELETE
  ON my_table
  FOR EACH ROW
  EXECUTE PROCEDURE notify_my_table_update();
  -- We can not use TRUNCATE event in this trigger because it is not supported in case of FOR EACH ROW Trigger 

4. 注册 my_table_update channel 以接收通知。

LISTEN my_table_update;

5. 现在您可以在 session 中的 PSQL 提示符上收到通知。

插入操作

TEST=# INSERT into my_table VALUES (1, 'TESTING');
INSERT 0 1
Asynchronous notification "my_table_update" with payload "OPERATION = INSERT and ID = 1" received from server process with PID 9057.

更新操作

TEST=# update my_table  set data='NOTIFY' where ID>=2;
UPDATE 2
Asynchronous notification "my_table_update" with payload "OPERATION = UPDATE and ID = 2" received from server process with PID 9057.
Asynchronous notification "my_table_update" with payload "OPERATION = UPDATE and ID = 3" received from server process with PID 9057.

删除操作

TEST=# delete from my_table ;
DELETE 3
Asynchronous notification "my_table_update" with payload "OPERATION = DELETE and ID = 1" received from server process with PID 9057.
Asynchronous notification "my_table_update" with payload "OPERATION = DELETE and ID = 2" received from server process with PID 9057.
Asynchronous notification "my_table_update" with payload "OPERATION = DELETE and ID = 3" received from server process with PID 9057.