In PostgreSQL I'm trying to make a database trigger on an INSERT
that passes the primary key of the new row as the payload of a channel notification using NOTIFY
I'm having trouble wrapping my head around the correct usage age of the NEW
variable in this context. No matter what I try seems to generate a syntax error.
Below is a simplified example of my use case.
CREATE OR REPLACE FUNCTION table_event_notify_function()
RETURNS trigger AS $$
BEGIN
NOTIFY table_event_notify, NEW.id;
return NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER table_events_insert_trigger AFTER INSERT ON table_events EXECUTE PROCEDURE table_event_notify_function();
psycopg2.errors.SyntaxError: syntax error at or near "NEW"
CodePudding user response:
The payload parameter for NOTIFY must be a constant. To provide a dynamic parameter, you need to use pg_notify()
perform pg_notifiy('table_event_notify', new.id::txt);