(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.)
I recall a prior discussion about making it possible to use triggers
on
views as a substitute for DO INSTEAD rules, by removing the
rewrite-time
check and only erroring out if we actually get to the point of
attempting a physical insert.
I had no error check fallback, but if I wanted one I suppose
I'd do a AFTER ... EACH ROW trigger that raised an exception
if it was ever called. I did do a BEFORE EACH STATEMENT
trigger that raised an exception and figured on deleting
it for those "views" that I made that needed BEFORE EACH
ROW triggers. Rules worked just fine for some views and
I was going to leave those, but allways do the views "my
way" for consistency instead of sometimes using CREATE VIEW
and sometimes not.
Then a BEFORE INSERT trigger could do
something appropriate with the data and return NULL to prevent the
error.
Exactly. My plan was to always have the trigger return NULL
so the underlying table would never be modified.
This seems like a good idea because triggers often are much
easier to work with than rules --- eg, there's no problem with
multiple
evaluations of volatile functions, even if you send the data to
several
places. However, I'm not sure that the idea scales to cover updates
and
deletes; with no concept of physical tuple identity (ctid) for the
view
rows, it's not clear that you can write triggers that will reliably do
the right things
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?)
Karl <kop@xxxxxxxx>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
