Re: Why can't I put a BEFORE EACH ROW trigger on a view?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why can't I put a BEFORE EACH ROW trigger on a view?
Date: 2007-02-25 01:48:58
Message-ID: 960.1172368138@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Karl O. Pinc" <kop(at)meme(dot)com> writes:
> why can't I put a SELECT rule on a table?

Because then it would be a view.

As for $SUBJECT, the problem is that there will never be an insert into
a view --- not at the level of a physical insert attempt anyway --- and
thus there is nothing for a trigger to do.

The reason there will never be an insertion trigger event is that we
reject any INSERT on a view that isn't rewritten (by an unconditional
DO INSTEAD rule) into something else.

I recall a prior discussion about making it possible to use triggers on
views as a substitute for DO INSTEAD rules, by removing the rewrite-time
check and only erroring out if we actually get to the point of
attempting a physical insert. Then a BEFORE INSERT trigger could do
something appropriate with the data and return NULL to prevent the
error. This seems like a good idea because triggers often are much
easier to work with than rules --- eg, there's no problem with multiple
evaluations of volatile functions, even if you send the data to several
places. However, I'm not sure that the idea scales to cover updates and
deletes; with no concept of physical tuple identity (ctid) for the view
rows, it's not clear that you can write triggers that will reliably do
the right things.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-02-25 01:56:34 Re: Why can't I put a BEFORE EACH ROW trigger on a view?
Previous Message Karl O. Pinc 2007-02-25 01:20:24 Re: Why can't I put a BEFORE EACH ROW trigger on a view?