RETURNING clause with BEFORE INSERT trigger

Question

I'm using table inheritance to split a table into smaller ones. I'm using BEFORE INSERT trigger to route new data into correct inherited tables. This trigger returns NULL so actual INSERT won't run on parent table.

The side effect of this is lack of any result of actual INSERT:

INSERT INTO TABLE a VALUES (...) RETURNING a_id

triggers BEFORE INSERT which directs new data to another table a_CURRENT_DATE - dynamically created when necessary by the trigger function. The trigger returns NULL so actual INSERT into table a is suppressed.

Original query has no result hence no a_id (a_id is a SERIAL column) is available.

What is the most elegant way to obtain a_id value?

Answer 1

Good question. That is typical problem with partitioning. I'm afraid there is no good, or elegant solution, and all you can do is to introduce some workarounds:

• inserting, and then deleting - yes, far from perfect,
• if you need id generated by serial type, you can use currval()... That would mean another query.

Here there is yet another other way - you can create view, and use instead of trigger for that view. It is hard to tell if that is elegant, but for me that is quite close to that.