--On 5. September 2010 09:09:55 +0100 Dean Rasheed
<dean.a.rasheed@gmail.com> wrote:
I had a first look on your patch, great work!
> Attached is an updated patch with more tests and docs, and a few minor
> code tidy ups. I think that the INSTEAD OF triggers part of the patch
> is compliant with Feature T213 of the SQL 2008 standard. As discussed,
Reading the past discussions, there was some mention about the RETURNING
clause.
I see Oracle doesn't allow its RETURNING INTO clause with INSTEAD OF
triggers (at least my 10g XE instance here doesn't allow it, not sure about
newer versions). I assume the following example might have some surprising
effects on users:
CREATE TABLE foo(id integer);
CREATE VIEW vfoo AS SELECT 'bernd', * FROM foo;
CREATE OR REPLACE FUNCTION insert_foo() RETURNS trigger
LANGUAGE plpgsql
AS $$ BEGIN INSERT INTO foo VALUES(NEW.id);RETURN NEW;
END; $$;
CREATE TRIGGER insert_vfoo INSTEAD OF INSERT ON vfoo FOR EACH ROW EXECUTE PROCEDURE insert_foo();
INSERT INTO vfoo VALUES('helmle', 2) RETURNING *; text | id
--------+----helmle | 2
(1 row)
SELECT * FROM vfoo;text | id
-------+----bernd | 2
(1 row)
This is solvable by a properly designed trigger function, but maybe we need
to do something about this?
> I don't plan to add the syntax to allow triggers on views to be
> disabled at this time, but that should be easy to implement, if there
> is a use case for it.
I really don't see a need for this at the moment. We don't have DISABLE
RULE either. I'm going to post some additional comments once i've
understand all things.
--
Thanks
Bernd
