Postgresql – How to return all column values for the recently inserted row/rows

ctedatabase-designpostgresqltrigger

I have these two tables in a PostgreSQL 12 database:

CREATE TABLE public.test (
    entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

CREATE TABLE public.test_registration (
    entity_id uuid REFERENCES test(entity_id),
    row_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    entry_name text,
    code text
);

entry_name is populated by a trigger AFTER INSERT:

CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger AS
$$
BEGIN
   UPDATE test_registration
   SET entry_name = new_inserts.code
   FROM new_inserts
   WHERE new_inserts.row_id = test_registration.row_id;
   RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert_trigger
AFTER INSERT ON test_registration
REFERENCING NEW TABLE AS new_inserts
FOR EACH STATEMENT
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE test_registration_entry_name();

The trigger function differs from table to table.

My current issue is when I insert a row into test_registration as such:

INSERT INTO test DEFAULT VALUES
RETURNING entity_id;  -- let's say: 'a17e66c5-1049-4ba9-bed3-90bbc823e064'

And then insert a registration and return all row values for the inserted row:

INSERT INTO test_registration (entity_id,code)
VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'EB')
RETURNING *

I get all column values correctly, except for the entry_name which is null. Which makes sense because entry_name is being set after the insert.

So I tried to expose this by altering my INSERT using the row_id as such:

WITH create_query AS (INSERT INTO test_registration (entity_id,code)
VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'13')
RETURNING *) SELECT v.entity_id, v.row_id, b.entity_id,b.entry_name, b.row_id 
from create_query AS v, test_registration AS b WHERE V.Row_id = b.Row_id

This then turns out to not return anything. create_query.row_id seems to differ from the one in test_registration – which makes no sense? Why would they be different?

How can I extract all column values for the inserted row, after the triggers has been triggered.

Best Answer

Impossible with an AFTER trigger

The version of the row visible to the RETURNING clause is the same version that's visible to an AFTER trigger. The UPDATE in your trigger function creates a new row version. It's impossible to see the effects in the RETURNING clause, which is based on the prior row version. Demo:

db<>fiddle here

Effectively, the output of RETURNING is "dead on arrival", obsoleted immediately by the AFTER trigger. You would need a separate SELECT to see the new row version. (Can be in the same transaction.)

Impossible with a CTE

Your query is bound to return nothing:

WITH create_query AS (
   INSERT INTO test_registration (entity_id,code)
   VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'13')
   RETURNING *
   )
SELECT v.entity_id, v.row_id, b.entity_id, b.entry_name, b.row_id 
FROM   create_query AS v, test_registration AS b
WHERE  v.row_id = b.row_id;

All sub-statements of a query with CTEs see the same snapshot of the database. Since the SELECT cannot yet see the row inserted in the CTE (and row_id is populated with uuid_generate_v4()) your implicit JOIN is bound to return no row.

See:

Proper trigger

I see nothing to warrant a STATEMENT-level, nor the need for an AFTER trigger. Use a much simpler (and faster) ROW-level trigger BEFORE. Then RETURNING * will just work:

CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.entry_name := NEW.code;
   RETURN NEW;
END
$func$;

CREATE TRIGGER test_insert_trigger
BEFORE INSERT ON test_registration
FOR EACH ROW
WHEN (pg_trigger_depth() = 0
      AND NEW.entry_name IS DISTINCT FROM NEW.code) -- optional
EXECUTE FUNCTION test_registration_entry_name();

db<>fiddle here

(The fiddle operates with integers because the additional module uuid-ossp providing uuid_generate_v4() is not installed on dbfiddle.uk. But it works all the same.)

I added another (optional) trigger condition: NEW.entry_name IS DISTINCT FROM NEW.code to avoid needless work if the trigger wouldn't change anything.

Related:

Aside: more efficient INSERT

Do both inserts in a single query with a data-modifying CTE like this:

WITH ins_test AS (
   INSERT INTO test DEFAULT VALUES RETURNING entity_id
   )
INSERT INTO test_registration (entity_id, code)
SELECT entity_id, 'EB'
FROM   ins_test
RETURNING *;

Then you can immediately reuse the auto-generated entity_id without another round-trip to the server. See:

Related Question