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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
