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