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

From: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why can't I put a BEFORE EACH ROW trigger on a view?
Date: 2007-02-25 00:16:56
Message-ID: 1172362616l.30468l.4l@mofo
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I want to do some additional data validation
when data is changed through a view, and
I want pretty exception messages, and I want to
do some slightly complex processing when
determining what data to update where in
what order.

So, I figured I'd make a table, put some
BEFORE EACH ROW triggers on it to do
all the tricky stuff, sometimes including
changing other tables, and then put a
SELECT rule on the table to make it look
like a view.

Turns out there's undocumented restrictions,
and this turns the table into a view which
then can't have triggers on it.

I can put other sorts of rules onto a table,
why can't I put a SELECT rule on a table?
What's the special case here?

It's probably conceivably possible to do
what I want using piles of functions
called by CHECK constraints, and possibly
many conditional rules, but that's
just grody.

For me, having a select rule on a table would
have additional benefit in that the front-end
would recognize the table as something that
can be modified. (PhpPgAdmin) As of now
it considers views as un-modifiable.

If a relation looks like a table in all respects,
why can't it _be_ a table? If it's a matter
of nobody having done the work I might
conceively possibly be able to come up
with a patch.

Any help would be appreciated.

Thanks.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-02-25 00:25:54 Re: Why can't I put a BEFORE EACH ROW trigger on a view?
Previous Message Matthew Peter 2007-02-24 20:24:48 Re: pgfoundry