Calling a function for each updated row in postgresql

Question

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

why PERFORM function_name() looks worse then SELECT function_name()?.. function performs UPDATE on a table, why SELECT UPDATE is better then PERFORM UPDATE?.. — Vao Tsun
– Vao Tsun, Commented Aug 3, 2015 at 9:28
@JakubKania: I'm not sure either, but I don't need the notifications to be transaction safe. The receiver checks if the row exists before doing anything with it. — Tim
– Tim, Commented Aug 3, 2015 at 10:12
@VaoTsun I'm not sure what you mean. I don't really care how it looks, but I did not find a solution to my problem using PERFORM — Tim
– Tim, Commented Aug 3, 2015 at 10:13
@a_horse_with_no_name I did not, but I would guess that it does not work, as only tables are allowed in the FROM clause — Tim
– Tim, Commented Aug 3, 2015 at 10:15

score 1 · Accepted Answer · 2016-03-17 22:15:09Z

1

"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 be tempted to use a 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've tested this and it works fine

edited Mar 17, 2016 at 22:15

answered Aug 3, 2015 at 10:17

user1123335

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Over a year ago

Good thought. This would actually solve the question as I stated it. What I did not say, however, is, that the receiver of the signal also sometimes inserts or updates rows for which I don't want him to be notified. I could, of course, add a mechanism to the receiver that checks wether it has generated the notification himself, but would prefer not to as it seems overly complicated.

Collectives™ on Stack Overflow

Calling a function for each updated row in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related