databasepostgresqltriggerscolumn-defaults

INSERT ...RETURNING .. comes up empty when BEFORE trigger cancels statement

I have a PostgreSQL before insert trigger on create that basically redirects inserts into sub-tables. Once I insert the record, I want to ABORT the request as to avoid duplicate data (by not inserting into parent table) so I am using return NULL in the trigger. The problem is that I need the record to be returned so I can get the ID. If I return NULL, I get NULL.

The above problem is discussed at below link: PostgreSQL trigger not returning anything

One of the answers says to insert into parent table (by not returning null but return new) and use AFTER insert trigger to delete it from parent table. But I am looking at 1000 writes per second and this may be a serious issue on performance because of the deletes.is there any other way to do this?

To be exact is there a way to return the id of a inserted row without inserting into the parent table and deleting it later.

Solution

  • I wrote the answer you are referring to. As I already hinted over there:

    You could also use a RULE ... INSTEAD .. for this purpose.

    RULE

    Rules can be tricky. I'd rather use triggers where possible. Be sure to read a bit, before you try this:

    CREATE OR REPLACE RULE tbl_ins AS
ON INSERT TO tbl
DO INSTEAD
INSERT INTO tbl2 (col1, col2, ...)  -- just do mention columns where ...
VALUES (NEW.col1, NEW.col2, ...)    -- ... you want to insert column defaults
RETURNING tbl2.*

    That would return values from tbl2 while avoiding phantom rows. However, per documentation on CREATE RULE:

    In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING clause that emits the view's columns. This clause will be used to compute the outputs if the rule is triggered by an INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING command respectively. When the rule is triggered by a command without RETURNING, the rule's RETURNING clause will be ignored. The current implementation allows only unconditional INSTEAD rules to contain RETURNING;

    Bold emphasis mine.
    Since you mention sub-tables, I take it you'd need conditions to distribute the inserts ...

    currval() / lastval()

    If you operate with a trigger FOR EACH ROW you can easily fetch appropriate values from sequences with currval() / lastval(). The tricky part is to return those values from a trigger function. I can only think of writing to a temporary table. Needs some thinking when to create and when to drop that one ...

    I would probably rethink the whole approach and redirect the data to multiple INSERT statements to actual target tables ...