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

On 02/24/2007 08:55:40 PM, Tom Lane wrote:
> "Karl O. Pinc" <kop(at)meme(dot)com> writes:
>
> NEW and OLD only include the user-visible columns. I'm not sure that
> that's sufficient. If you assume that the view exposes a primary key
> for each of its underlying tables, then you could use the pkey values
> to find and update a row in the underlying table(s), but this is not
> exactly guaranteeing that you're updating the same row that the view
> query saw initially. And what if you don't want the view to expose
> the pkey?

These are INSERT, UPDATE, and DELETEs, so why not get rid of
the SELECT column criteria and modify the query plan to
come up with a SELECT * ? In other words, why not expose everything
to the trigger? You'd only be doing this if there wasn't
otherwise a rule for, say, UPDATE, on the view anyhow.

Oh. You'd have to keep any non-columm expressions that the view
exposes. NEW sounds easy, but I don't know about OLD.
Somehow the rules are doing something for OLD now.

You'd have to come up with column name
conventions (or some syntax)
for NEW and OLD to handle conflicts should some
joined tables have non-unique column names. (The ones exposed
by the view already have names.)
I don't know the right way to approach
this problem but it feels tractable. (To somebody who's
very unlikely to be writing the code. FYI, you're like the
Pg-General-List-Code-Angel.)

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