This topic came up before
http://archives.postgresql.org/pgsql-general/2010-12/msg00542.php
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
with :
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.
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
