代码之家 › 专栏 › 技术社区 › Ryabchenko Alexander

Postgres notify:在表中的创建/删除/更新行上使用行id进行通知

listen notify postgresql

0

Ryabchenko Alexander · 技术社区 · 6 年前

我想创造 LISTE/NOTIFY 管道与 trigger_function 已发送 NOTIFY .

在我的 通知 我想从row那里得到消息 id 对于任何 create/delete/update 表中有一行。

我该怎么写这样的通知 触发器函数 ?

到目前为止,我有下一次移民吗?创建无行触发器 身份证件

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();

0 回复 | 直到 6 年前

1

8

Ryabchenko Alexander 6 年前

步骤如下:

创建表格 my_table

CREATE TABLE my_table(
id int,
data varchar
)

然后写 Trigger Procedure :

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;

在表上创建后触发器 initial_cost_tasks 对于 INSERT / UPDATE / DELETE

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

注册 my_table_update 接收通知的通道。

LISTEN my_table_update;

现在,您可以在您的 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.