Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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

share|improve this question
    
I'm not really sure if that's transaction safe. – Jakub Kania Aug 3 '15 at 8:59
1  
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 Aug 3 '15 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 Schneider Aug 3 '15 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 Schneider Aug 3 '15 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 Schneider Aug 3 '15 at 10:15

"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

share|improve this answer
    
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. – Tim Schneider Aug 3 '15 at 10:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.