insert/update/delete returning and rules

From: "Jaime Casanova" <systemguards(at)gmail(dot)com>
To: "Pgsql Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: insert/update/delete returning and rules
Date: 2006-08-15 16:27:10
Message-ID: c2d9e70e0608150927l43746a16s6339b71b88cd1da4@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I'm doing some tests of Bernd's updatable views patch and found
something interesting about the RETURNING behavior

testing_uv=# create table bar (field1 integer);
CREATE TABLE
testing_uv=# create view v_bar as select * from bar;
CREATE VIEW

the rules are created as:

"_DELETE" AS
ON DELETE TO v_bar DO INSTEAD DELETE FROM ONLY bar
WHERE
CASE
WHEN old.field1 IS NOT NULL THEN old.field1 = bar.field1
ELSE bar.field1 IS NULL
END

"_INSERT" AS
ON INSERT TO v_bar DO INSTEAD INSERT INTO bar (field1)
VALUES (new.field1)

"_UPDATE" AS
ON UPDATE TO v_bar DO INSTEAD UPDATE ONLY bar SET field1 = new.field1
WHERE
CASE
WHEN old.field1 IS NOT NULL THEN old.field1 = bar.field1
ELSE bar.field1 IS NULL
END

Now, if i insert directly into the table i get:

testing_uv=# insert into bar values (1), (2) returning *;
field1
--------
1
2
(2 rows)

INSERT 0 2

but if i insert using the rules the returning clause is ignored

testing_uv=# insert into v_bar values (3), (4) returning *;
INSERT 0 2

any comments?

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2006-08-15 16:30:32 Re: [PATCHES] Custom variable class segmentation fault
Previous Message Peter Eisentraut 2006-08-15 16:26:39 Re: An Idea for planner hints