PLPGSQL function returning trigger AND value

Question

I have written a PL/PGSQL function that returns a trigger, so I can call it before each row insert. I realize now that I would also like that function to return the ID of the newly inserted row. I'm not quite sure how to proceed since my function must return a trigger. Here's some code:

CREATE OR REPLACE FUNCTION f_insert_album() RETURNS TRIGGER AS $$
DECLARE
    subj_album_id INTEGER;
BEGIN
    -- ... some parts where left out
    INSERT INTO t_albums_subjective (user_id, album_id, format_id, location_id, rating) 
    VALUES (NEW.user_id, obj_album_id, NEW.format_id, NEW.location_id, NEW.rating) 
    RETURNING id INTO subj_album_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Bind insert function to trigger
DROP TRIGGER IF EXISTS tr_v_albums_insert ON v_albums; 
CREATE TRIGGER tr_v_albums_insert INSTEAD OF INSERT ON v_albums
    FOR EACH ROW EXECUTE PROCEDURE f_insert_album();

I must keep the return type of my function f_insert_album() to TRIGGER, but I would really like to also return the value in subj_album_id, corresponding to the id of the newly inserted row.

Is there anyway to do this? Is it even possible? Obviously changing the return type didn't work with Postgres. Could you suggest an alternative approach if any?

You'd have to store the generated identifier into the tuple that fired the trigger, if there's an appropriate field, and use INSERT ... RETURNING... for the top level query too. Or use a notification payload via LISTEN and NOTIFY. — Craig Ringer
– Craig Ringer, Commented Nov 2, 2015 at 3:35
Thanks Craig, although I'm not quite sure I understand very well ! Could you please elaborate your first idea ? — John Pink
– John Pink, Commented Nov 2, 2015 at 13:51

Erwin Brandstetter · Accepted Answer · 2021-03-21 00:42:26Z

1

The crucial question: where to return the ID to?

Assuming you want to return it from the INSERT statement directly, then you are almost there. You already assign the newly generated ID to a function parameter:

...
RETURNING id INTO subj_album_id;

Instead, assign it to a column of the row firing the trigger. The special variable NEW holds this row in a trigger function:

...
RETURNING id INTO NEW.album_id;  -- use actual column name in view

Then use the RETURNING clause of the INSERT statement:

INSERT INTO v_albums (user_id, format_id, location_id, rating) 
VALUES ( ... ) 
RETURNING album_id;

Obviously, this is only possible if there is a visible column in the view. It does not have to be assigned in the INSERT command, though. The type of the NEW row variable is defined by the definition of the view, not by the INSERT at hand.

Closely related:

RETURNING data from updatable view not working?

For what you seem to be doing (grant access to certain rows of a table to a certain role) row level security (RLS) in Postgres 9.5 or later might be a more convenient alternative:

edited Mar 21, 2021 at 0:42

answered Nov 2, 2015 at 15:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

John Pink Over a year ago

Thanks a billion ! This worked, and the closely related question helped a lot.

Collectives™ on Stack Overflow

PLPGSQL function returning trigger AND value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related