This topic came up before
and there was some discussion on how-to.
Briefly, the table is partitioned and there is an id column declared as
id bigint DEFAULT nextval('history_id_seq'::regclass) NOT NULL
and the application issues
"INSERT into history (column-list which excludes id) values (....) RETURNING id"
In our case, the table is not (yet) partitioned and this RETURNING clause works,
but I need to partition the table. And one of the requirements is that the
application source code must not need to be changed, so e.g. not allowed to make
it explicitly fetch nextval('history_id_seq').
I am running postgresql-9.2beta2
I can get the re-direction of the INSERT *without* RETURNING to work
using either trigger or rule, in which the trigger/rule invokes a procedure,
but whichever way I do it, I could not get this RETURNING clause to work.
For a trigger, the INSERT ... RETURNING was accepted but returned no rows,
(as I would expect), and for the RULE, the INSERT ... RETURNING was rejected
ERROR: cannot perform INSERT RETURNING on relation "history"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
but this hint was not much help, since :
CREATE RULE insert_part_history as ON INSERT to history DO INSTEAD SELECT history_insert_partitioned(NEW) returning NEW.id
ERROR: syntax error at or near "returning"
LINE 1: ...DO INSTEAD SELECT history_insert_partitioned(NEW) returning ...
In looking at what was happening for the RULE scenario, I found I could get it to work by making some relatively
straightforward changes to the postgresql source code in the areas of rewrite, planner and portal,
basically just to set and propagate a flag to encourage all those components that my rewritten query
really would return something so let it do it. All the infrastructure for making this work,
i.e. actually building the result tuple and sending it to the output stream, was already there.
Is anyone else working on this or are there any plans to support it? It does seem to be a useful capability.
I can send my patch in the the hackers list if there is any interest.
pgsql-general by date
|Next:||From: Tom Lane||Date: 2012-06-15 16:50:08|
|Subject: Re: parsing SQLERRM ?|
|Previous:||From: Thomas Boussekey||Date: 2012-06-15 16:33:49|
|Subject: Re: Starting a cluster as a service|