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

On 2/24/07, Karl O. Pinc <kop@meme.com> wrote:
> (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.)
>

http://www.postgresql.org/docs/current/static/rules-views.html

Actually, i found it very clear: if you create a SELECT rule on a
table it becomes a view, this is what postgres does every time you
create a view


> 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?)
>

why not simply create BEFORE TRIGGERS on the base table, CREATE VIEW
on top and use an INSERT/UPDATE/DELETE rule on the view to rewrite
those operations to the equivalents on the tables... that way you will
have your TRIGGERS validating the data...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook