I have a sql UPDATE
statement in a plpgsql function. I now want to call the pg_notify function for each updated row and am uncertain if my solution is the best possibility.
I am not aware of any position in the UPDATE
statement itself where I could apply the function. I don't think it is possible in the SET
part and if I would apply the function in the WHERE
part, it would be applied to each row as it is checked and not only the updated rows, correct?
I therefore thought I could use the RETURNING
part for my purposes and designed the function like this:
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 am not using or storing the return value of the query anywhere. I therefore tried putting PERFORM
in front of the query but this seemed to be syntactically incorrect.
After trying different combinations with PERFORM
my ultimate 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 it is supposed to, but I feel like there should be a better solution which does not temporarily store a useless result and does not use a useless variable.
Thank you for your help.
** EDIT 1 **
As can be seen in @pnorton 's answer, a trigger would do the trick in most cases. For me, however, it is not applicable as the receiver of the notifications also sometimes updates the table and I do not want to generate notifications in such a case
PERFORM
– Tim Schneider Aug 3 '15 at 10:13FROM
clause – Tim Schneider Aug 3 '15 at 10:15