To: Richard Huxton <dev@archonet.com>
cc: Ken Winter <ken@sunward.org>,
	"'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
Subject: Re: Rule causes baffling error 
In-reply-to: <43A6DA39.2080509@archonet.com> 
References: <009c01c604b3$1b007630$6603a8c0@kenxp>
	<43A6DA39.2080509@archonet.com>
Comments: In-reply-to Richard Huxton <dev@archonet.com>
	message dated "Mon, 19 Dec 2005 16:05:13 +0000"
Date: Mon, 19 Dec 2005 11:27:30 -0500
Message-ID: <29898.1135009650@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Richard Huxton <dev@archonet.com> writes:
> Ken Winter wrote:
>> Can a trigger be written on a *view*?

> There's nothing for them to fire against even if you could attach the 
> trigger.

Currently we reject CREATE TRIGGER on a view, but it occurred to me the
other day that that could be relaxed, at least for BEFORE triggers.
The system could feed the trigger with the synthetic view row, and the
trigger could update the view's underlying tables and then return NULL
to suppress any actual "operation" on the view proper.

To do this, instead of erroring out in the rewriter if a view has no
DO INSTEAD rule, we would have to error out down in the guts of
the executor if control got as far as trying to actually
insert/update/delete a tuple in a view.

The trickiest part of this is probably generating the "old" row for
UPDATE and DELETE cases.  I think you'd need to adjust the planner
so that it would generate all the "old" view columns, rather than
the current situation in which it generates just the "new" columns
for an UPDATE, or no columns at all (only the CTID) for a DELETE.
I don't see any fundamental reason why this couldn't be made to work
though.

Triggers would be better than rules for quite a few view-rewriting
scenarios, mainly because you'd avoid all the gotchas with double
evaluation and so on.  So it seems like it might be worth doing.

			regards, tom lane