BEFORE Trigger + RETURNING returns NULL

Question

This is one of the BEFORE triggers that inserts into the right table partition:

CREATE OR REPLACE FUNCTION public.insert_install_session()
  RETURNS trigger
  LANGUAGE plpgsql
AS
$body$
BEGIN
    IF (NEW.created >= '2015-10-01 00:00:00' AND NEW.created < '2015-10-02 00:00:00') THEN
        INSERT INTO install_session_2015_10_01 VALUES (NEW.*);
    ELSIF (NEW.created >= '2015-10-02 00:00:00' AND NEW.created < '2015-10-03 00:00:00') THEN
        INSERT INTO install_session_2015_10_02 VALUES (NEW.*);
    ELSIF (NEW.created >= '2015-09-30 00:00:00' AND NEW.created < '2015-10-01 00:00:00') THEN
        INSERT INTO install_session_2015_09_30 VALUES (NEW.*);
    ELSE
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$body$

CREATE TRIGGER trigger_insert_install_session
    BEFORE INSERT ON install_session
    FOR EACH ROW EXECUTE PROCEDURE insert_install_session

and I have a query that uses RETURNING:

INSERT INTO "install_session"
  (<columns here>)
VALUES
  (<values here>)
RETURNING "install_session"."id";

How can I make the RETURNING work? It seems it always returns NULL.

Is it because of the RETURN NULL at the end of the function? I can't return NEW because the row would be inserted a second time, no? Here is the official docs.

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

This is not going to work with a trigger solution. You could make it work with rules instead of triggers IIRC, but that has other caveats ...

However, to just get the auto-generated ID from a serial column, you can call currval() immediately after the command in the same session:

SELECT currval('name_of_your_id_sequence_here'::regclass);

Or even just lastval() - if respective id columns in the partitions are not inherited (don't share the same sequence).

SELECT lastval('name_of_your_id_sequence_here'::regclass);

You can use pg_get_serial_sequence() to find the name of the sequence if you don't know it:

SELECT currval(pg_get_serial_sequence('install_session', 'id'));

BEFORE Trigger + RETURNING returns NULL

1 Answers1