Re: proposal and patch : support INSERT INTO...RETURNING with partitioned table using rule

On Wed, Jun 20, 2012 at 12:24 PM, John Lumby <johnlumby(at)hotmail(dot)com> wrote:
>     An INSERT which has a RETURNING clause and which is to be rewritten based on
>     a rule will be accepted if the rule is an "unconditional DO INSTEAD".
>     In general I believe "unconditional" means "no WHERE clause", but in practice
>     if the rule is of the form
>        CREATE RULE insert_part_history as ON INSERT to history \
>          DO INSTEAD SELECT history_insert_partitioned(NEW) returning NEW.id
>     this is treated as conditional and the query is rejected.

This isn't rejected because the query is treated as condition; it's
rejected because it's not valid syntax. A SELECT query can't have a
RETURNING clause, because the target list (i.e. the part that
immediately follows the SELECT) already serves that purpose. The fact
that it's in a CREATE RULE statement is irrelevant:

rhaas=# select 4 returning 3;
ERROR: syntax error at or near "returning"
LINE 1: select 4 returning 3;
^

>   .  I propose to extend the rule system to recognize cases where the INSERT query specifies
>      RETURNING and the rule promises to return a row,  and to then permit this query to run
>      and return the expected row.   In effect,  to widen the definition of "unconditional"
>      to handle cases such as my testcase.

That already (kind of) works:

rhaas=# create table history (id bigserial, name text);NOTICE: CREATE
TABLE will create implicit sequence "history_id_seq" for serial column
"history.id"
CREATE TABLE
rhaas=# create table history1 () inherits (history);
CREATE TABLE
rhaas=# create rule history_insert as on insert to history do instead
insert into history1 (id, name) values (NEW.id, NEW.name || ' is
awesome!') returning 17::bigint, 'cheeze whiz'::text;
CREATE RULE
rhaas=# insert into history (name) values ('Linus') returning id,
name; id | name
----+-------------
17 | cheeze whiz
(1 row)

INSERT 0 1
rhaas=# select * from history;
id | name
----+-------------------
1 | Linus is awesome!
(1 row)

I do notice that the RETURNING clause of the INSERT can't reference
NEW, which seems like a restriction that we probably ought to lift,
but it doesn't seem to have much to do with your patch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company