Function call for each updated row in postgresql

Question

I have a sql UPDATE in the plpgsql function. Now I want to call the pg_notify function for each updated line, and I'm not sure if my solution is the best option.

I do not know of any position in the UPDATE expression itself where I could apply this function. I don’t think that this is possible in the SET part, and if I applied the function in the WHERE part, it will be applied to each line, how it is checked, and not only updated lines, right?

Therefore, I thought that I could use the RETURNING part for my purposes and developed such a function:

 CREATE OR REPLACE FUNCTION function_name() RETURNS VOID AS $BODY$ BEGIN UPDATE table1 SET a = TRUE FROM table2 WHERE table1.b = table2.c AND <more conditions> RETURNING pg_notify('notification_name', table1.pk); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; 

Unfortunately, this gave me an error saying that I do not use or save the return value of the request anywhere. So I tried to put PERFORM before the request, but that seemed syntactically wrong.

After several combinations with PERFORM my final solution is this:

 CREATE OR REPLACE FUNCTION function_name() RETURNS VOID AS $BODY$ DECLARE dev_null INTEGER; BEGIN WITH updated AS ( UPDATE table1 SET a = TRUE FROM table2 WHERE table1.b = table2.c AND <more conditions> RETURNING pg_notify('notification_name', table1.pk) ) SELECT 1 INTO dev_null; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; 

This works as intended, but I feel that there should be a better solution that temporarily does not save the useless result and does not use the useless variable.

Thank you for your help.

** EDIT 1 **

As seen from @pnorton's answer, a trigger will do the trick in most cases. However, for me this is not applicable, since the notification recipient also sometimes updates the table, and I do not want to generate notifications in this case

Answer 1

"I have an UPDATE sql statement in the plpgsql function. Now I want to call the pg_notify function for each updated row "

Ok, maybe I’ll be tempted to use the Eg trigger

  CREATE TABLE foobar (id serial primary key, name varchar); CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$ DECLARE BEGIN PERFORM pg_notify('watch_tb_update', TG_TABLE_NAME || ',id,' || NEW.id ); RETURN new; END; $$ LANGUAGE plpgsql; CREATE TRIGGER foobar_trigger AFTER INSERT ON foobar FOR EACH ROW EXECUTE PROCEDURE notify_trigger(); LISTEN watch_tb_update; INSERT into foobar(id, name) values(1,'test_name'); 

I tested this and it works great