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

On 02/24/2007 11:24:40 PM, Jaime Casanova wrote:
> On 2/24/07, Karl O. Pinc <kop(at)meme(dot)com> wrote:

> 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

It does say that. But it does not say that if you do it exactly
the same thing will happen. I figured postgres does that internally,
when it knows it wants to make a view, but
if I did it explicitly I'd have a table with a select rule on it
that would operate just like a view but would still be a table.

And why not let me have a table with a select rule on it that
makes the table act like a view, but that I can otherwise
fiddle with myself, like adding triggers, and make of it
what I want? Then it's up to me to expose the foreign
keys or whatever else the triggers need to work.
That would be the painless solution that would work just
fine for my purposes, however nice it'd be to be able
to put BEFORE triggers on VIEWS -- after exposing all
columns of the underlying tables etc. (As I fantasized
about in a previous post.)

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

Because the view has columns that the underlying table does not,
that are computed but that I might want to update through,
validate, etc. See my previous post. Somebody already asked
this exact question.

I could write triggers on all my underlying tables that
do the "instantiated view" thing and update another table
with all the right info. And _then_ do what
you suggest, being sure to pass the data back
to the true underlying tables. (Actually, at that
point there'd be no point in CREATE VIEW
at all.) But that's a lot of work
and why go to the trouble when an ordinary view
will do just fine (as far as output goes anyway,
modification is where I have trouble.)
Having a table with real data in it seems like
a lot of overhead I don't need.

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