Re: INSERT INTO...RETURNING with partitioned table based on trigger function

On 2010-12-16, pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com <pgsql(dot)30(dot)miller_2555(at)spamgourmet(dot)com> wrote:
> --0015174c1e4aaf077604977d7e62
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi -
>
> Issue:
> How to return a sequence value generated upon INSERT of records into a
> partitioned table using trigger functions (without having to insert into the
> child table directly).

partitioning doesn't work with "INSERT ... RETURNING ..." and trigger
based partitioning.

use a rule instead have the rule FOR EACH ROW DO INSTEAD

SELECT insertfunc(NEW)

and have insertfunc do the insert and return the id column.

for declaring the function the type of NEW is table_name%ROWTYPE

> 2) multiple instances of the application may be running, so generation
> of the sequence number in the application is not feasible (moreover, the
> application is multi-threaded and additional summary data insertions may
> occur between the insertion of summary data and detailed data in the two
> partitioned tables.

another option is the application could call nextval itself or call
lastval after the insert. both of these SQL functions are thread safe.

> 3) is there a technical reason as to why the return values of trigger
> functions are ignored when TG_OP=AFTER for INSERT/ UPDATE operations,

because you can't change history.

--
⚂⚃ 100% natural