Re: Why can't I put a BEFORE EACH ROW trigger on a view? - Mailing list pgsql-general

(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@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein