On Wed, Jun 20, 2012 at 12:24 PM, John Lumby <johnlumby@hotmail.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