在Postgres更新触发器中检测列更改 [英] Detecting column changes in a postgres update trigger

问题描述

我有一个postgres数据库，其中包含多个表，我要监视这些表的更新，如果有任何更新，我想触发一个嘿，某些更改"更新.这在基本情况下有效，但是现在是时候进行改进了.

I have a postgres database with several tables that I want to watch for updates on, and if there's any updates, I want to fire a "hey, something changed" update. This works in the basic case, but now it's time to improve things.

CREATE FUNCTION notify_update() RETURNS trigger AS $notifyfunction$
BEGIN
  PERFORM pg_notify('update_watchers',
    $${"event":"update", "type": "$$ || TG_TABLE_NAME || $$", "payload": {"id": $$ || new.id || $$}}$$);
  RETURN new;
END;
$notifyfunction$ LANGUAGE plpgsql;

工作正常.我将它附加到桌子上，如下所示:

works just fine. I attach it to the table like so:

CREATE TRIGGER document_update_body
AFTER UPDATE ON documents
FOR EACH ROW EXECUTE PROCEDURE notify_update();

(作为一个附带的问题:如果有比触发函数中的mess'o'$$更好/更容易的方法来对触发结果进行json.stringify我的字符串化，请让我知道.平衡引号并不有趣)

(As a side-question: if there's any better / easier way to json.stringify my trigger result than the mess'o'$$ in the trigger function, please let me know. Balancing quotation marks isn't fun).

我想要做的是附加到pg_notify调用中已更改列的列表.除了遍历表中的列并检查NEW.col是否与OLD.col不同之外，它没有似乎的方法.这样做的不好方法是在我的通知过程中硬编码列名(易碎，如果更改架构，则需要更新其他内容，等等).

What I want to do is attach to the pg_notify call a list of the columns that have changed. It doesn't seem like there's any simple way to do this other than iterating over the columns in the table and checking if NEW.col is distinct from OLD.col. The bad way to do this would be to hard-code the column names in my notify procedure (fragile, another thing to update if I change my schema, etc).

的确，我也不擅长编写plpgsql，所以我不确定在哪里寻求帮助.理想情况下，(如果没有我在文档中没有看到的updated_columns块变量)，有一种方法可以将表的模式获取到通知块内，而又不会导致过多的性能开销(因为这些表将被更新一点).

I'm also out of my depth on writing plpgsql, really, so I'm not sure where to look for help. Ideally, (if there's no updated_columns block variable that I didn't see in the documentation) there'd be a way to get the table's schema inside the notification block without causing too serious of a performance overhead (as these tables will get updated a fair bit).

推荐答案

在

Read up on the hstore extension. In particular you can create a hstore from a row, which means you can do something like:

changes := hstore(NEW) - hstore(OLD);
...pg_notify(... changes::text ...)

信息多于您想要的信息(包括新值).如果只需要按键，可以使用akeys(changed).

That's slightly more information than you wanted (includes new values). You can use akeys(changed) if you just want the keys.

这篇关于在Postgres更新触发器中检测列更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！