PostgreSQL: NOTIFY when specific column is updated

Question

I currently have a table in PostgreSQL that looks like this

my_table (id, info, data, url)

This is being regularly updated with a webscraper, I have attached the following triggers to notify me on updates to any record

cursor.execute("""
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;
""")

and

# CREATE TRIGGER trigger_my_table_update
#   AFTER UPDATE
#   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 
#   """
# )

This sends me an async whenever the database is updated. However, I would only like it to notify me ONLY if there is any change in the columns data & url, that is the current values are being updated to new values. My web scraper updates these values regardless if the values scraped are different from the ones in the database.

How can I achieve this? I saw Richard Huxton's answer here recommend hstore. However, I am having trouble figuring out how to establish an old hstore as well as a new hstore for each record of this table.

I am using Python with psycopg2 if that makes any difference.

Laurenz Albe · Accepted Answer · 2021-10-22 14:58:45Z

3

It is simpler than you think:

IF OLD IS DISTINCT FROM NEW THEN
   PERFORM pg_notify('my_table_update',output);
END IF;

Use IS DISTINCT FROM rather than <> to handle NULL values correctly.

answered Oct 22, 2021 at 14:58

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gustaf Over a year ago

Thank you! How can I pass the column name that is being changed into the notify.payload? For example, I want the output to include my_data since that is the column being changed Currently it is: output = 'OPERATION = ' || TG_OP || ' and ID = ' || row.id;

Laurenz Albe Over a year ago

You could add to_jsonb(OLD) and to_jsonb(NEW) as payload, if it fits.

Laurenz Albe Over a year ago

Also, you can use something like this to dynamically extract the columns from OLD and NEW, but that's not exactly cheap.

Collectives™ on Stack Overflow

PostgreSQL: NOTIFY when specific column is updated

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related