Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 2010-12-16, pgsql.30.miller_2555@xxxxxxxxxxxxxxx <pgsql.30.miller_2555@xxxxxxxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux