CODEWITHSUNDEEP
sqlpostgresqlplpgsql
pfooti
pfooti

Reputation: 2704

Detecting column changes in a postgres update trigger

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

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

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).

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).

Upvotes: 9

Views: 7913

Answers (3)

Demian Martinez
Demian Martinez

Reputation: 531

Another way is to exploit JSON/JSONB functions that come in recent versions of PostgreSQL. It has the advantage of working both with anything that can be converted to a JSON object (rows or any other structured data), and you don't even need to know the record type.

See my original StackOverflow post with appropriate examples.

Upvotes: 1

Greg
Greg

Reputation: 6759

http://www.postgresql.org/docs/9.3/static/plpython-trigger.html

TD["table_name"]

I do exactly the same type of notify, I loop through all of the columns like this:

    for k in TD["new"]:
        if TD["old"][k] != TD["new"][k]:
            changed.append(k)

changed.append(k) builds my notification string. Somewhere else I do a listen, then broadcast the results out pub/sub to web socket clients.

-g

Upvotes: 1

Richard Huxton
Richard Huxton

Reputation: 22893

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 ...)

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

Upvotes: 10

Related Questions