Mono a function for each updated row in PostgreSQL

< p>“I have a sql UPDATE statement in a plpgsql function. I now want to
call the pg_notify function for each updated row “

, I might want to use trigger Eg

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 have tested this and it works fine

< /div>

I have a sql UPDATE statement in the plpgsql function. I now want to call the pg_notify function for each updated row, and am not sure if my solution is the best possibility.

I don’t know where in the UPDATE statement I can apply the function. I don’t think it is possible in the SET part, if I will apply the function in the WHERE part, it will be applied to every checked One line, not just the updated line, right?

So I think I can use the RETURNING part for my purpose and design the following 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 
 RETURNING pg_notify('notification_name', table1.pk);
END;
$BODY$LANGUAGE'plpgsql' VOLATILE;

Unfortunately, this gave me an error , Saying that I did not use or store the return value of the query anywhere. Therefore, I tried to put PERFORM in front of the query, but this seems to be syntactically incorrect.

I am trying to be different from PERFORM After combining, my ultimate solution is:

CREATE OR REPLACE FUNCTION function_name() RETURNS VOID AS $BODY$
DECLARE
 dev_null INTEGER;< br />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 should work as expected, but I feel There must be a better solution, it will not temporarily store useless results, and will not use useless variables.

Thank you for your help.

**Edit 1 **

As can be seen from @pnorton’s answer, in most cases, the trigger can solve the problem. However, for me, it does not apply because the recipient of the notification is sometimes updated Form, I don’t want to generate a notification in this case

“I have a sql UPDATE statement in a plpgsql function. I now want to
call the pg_notify function for each updated row “

OK, I might want to use trigger Eg

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 have tested this and it works fine