From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: WIP: Triggers on VIEWs |
Date: | 2010-09-22 22:16:52 |
Message-ID: | 34A06DC4F29B4D4C2E194F2D@amenophis |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
--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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2010-09-22 22:31:27 | Re: Configuring synchronous replication |
Previous Message | Alvaro Herrera | 2010-09-22 22:12:54 | Re: Git conversion status |