PostgreSQL: NOTIFY when specific column is updated-CodePudding

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.

CodePudding user response：

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.