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

On 2/24/07, Karl O. Pinc <kop(at)meme(dot)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