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

(Important stuff last.)

On 02/24/2007 07:48:58 PM, Tom Lane wrote:
> 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.

(Yup. But I tried to make my own view implimentation by putting
a SELECT rule on a table and that failed because I couldn't
make a BEFORE EACH ROW trigger.

"Problems putting a SELECT rule on a table" would be
just another way to phrase $SUBJECT.

The annoying part was that I spent much of the day assuming it
would work and then when I tested what I wanted I found that
the CREATE RULE statement required that I supply "_RETURN"
as a rule name, and that then it wouldn't create the
rule anyway because the table had triggers. Usually this
sort of thing happens when I mis-read the docs, but this
time the docs provided no warning.)

> 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.

I had no error check fallback, but if I wanted one I suppose
I'd do a AFTER ... EACH ROW trigger that raised an exception
if it was ever called. I did do a BEFORE EACH STATEMENT
trigger that raised an exception and figured on deleting
it for those "views" that I made that needed BEFORE EACH
ROW triggers. Rules worked just fine for some views and
I was going to leave those, but allways do the views "my
way" for consistency instead of sometimes using CREATE VIEW
and sometimes not.

Then a BEFORE INSERT trigger could do
> something appropriate with the data and return NULL to prevent the
> error.

Exactly. My plan was to always have the trigger return NULL
so the underlying table would never be modified.

> 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

I started with inserts, ran into problems, and came
screaming to the list for help. (Thanks.) I somehow assumed
that I'd be able to get a hold of NEW.* and OLD.* in my triggers
-- because they were there for me in when I first tried to
impliment the logic with rules. How is it that the rules
can come up with NEW and OLD for a view and why wouldn't
something be able to give triggers the same data. (At
least BEFORE ... EACH ROW triggers, we wouldn't care about
other triggers, would we?)

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein