2

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.

Community
  • 1
  • 1
Sarat Kiran
  • 23
  • 3

1 Answers1

0

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 ...

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the reply.. I was afraid of using rules since it doesn't support COPY, we do make backups and restore of database and rules make it difficult since rules are not activated by the COPY command. One quick question currval() returns the id inserted but if I inserted multiple rows in a single insert statement, will it return all the ids of the bulk insert ?? Thanks – Sarat Kiran Jun 05 '14 at 15:46
  • @user3654243: I added some more to address that. – Erwin Brandstetter Jun 05 '14 at 15:57
  • Thank you.. probably I will go with "redirect the data to multiple INSERT statements to actual target tables ..." – Sarat Kiran Jun 05 '14 at 16:06