Re: WIP: Triggers on VIEWs

--On 5. September 2010 09:09:55 +0100 Dean Rasheed 
 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