Search code examples
postgresqltriggersplpgsqlsql-viewsql-returning

PLPGSQL function returning trigger AND value

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?

Solution

  • 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:

    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: