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
>

Thanks. I had attempted to use rules prior to the trigger
implementation, but opted for the trigger-based implementation due to
easier maintenance (specific to this particular database, at least).

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

This is a great idea, and the one I'll probably end up implementing.
Many thanks for the suggestion!

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

I agree that handling the return value of a trigger function when
TG_OP=AFTER in such a way that alters the database itself does not
make sense on its face, though I think that allowing trigger-defined
return values to pass back to the trigger caller (and so on trough the
call stack back to the external application) is a reasonable
behaviour.