Re: WIP: Triggers on VIEWs

--On 5. September 2010 09:09:55 +0100 Dean Rasheed
<dean(dot)a(dot)rasheed(at)gmail(dot)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