Hi Tom,
It does seem they have a few short comings but at the same time they
hold a lot of promise as well.
I also realised that using a before trigger on the view wouldn't work
because the view doesn't contain all the columns in the underlying table
that will end up being set.
The best course of action seems to be to use a simple rewrite rule on
the trigger to just insert the data as is on the underlying table and
put the before trigger on the underlying trigger however I have two
things to solve:
1) my before trigger needs to work on a subset of my table, but I think
I'll be able to handle using the triggers condition
2) my code is handling a bunch of BI that currently happens client side
in legacy products talking to this database that I'm trying to move
server side. For some time the legacy code will live side by side with
the new implementation through the views and that introduces the risk of
doubling up on logic. Adding everything to the view was a nice way to
segregate the two approaches, I'll have to think of something new:)
Just thinking ahead as after solving the insert I'll be doing something
similar for updates on the view. Is there a way to detect which columns
are added to the "SET" part of my query (even if the value remains
unchanged) and will that survive the rewrite in the rule?
I.e. if my view has 15 columns but I do an;
update myView set col2 = 123, col5 = 567 where primarykey = 891;
Will I be able to know in my before update trigger on the table I'm
updating that those are the only columns listed, which I guess is extra
tricky because the column name of the view will not match that of the
update?
Cheers,
Bas
On 17/09/2015 2:14 pm, Tom Lane wrote:
> Bastiaan Olij <bastiaan@basenlily.me> writes:
>> Would a before insert trigger work on a view as well?
> Ah, sorry, wasn't paying close-enough attention.
>
> For a view, you can only provide an INSTEAD OF trigger, and that feature
> only exists in fairly recent PG releases. But if you're running such a
> release then I recommend looking into that way. Rules are, um, not the
> best part of Postgres.
>
> regards, tom lane
>
>
--
Kindest Regards,
Bastiaan Olij
e-mail: bastiaan@basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij