Dmitry Tkach <[EMAIL PROTECTED]> writes:Ok. If so, should UNION not be disallowed entirely inside (at least conditional) rules, regadless of whether it has those 'cross-from' references or not?
Sure, but it is inside the rule that has 'where x is not null and y is not null' on it as a qualifier, so
with my test example it should just never get executed in the first place.
You're confusing rules with triggers. The INSERT *will* get executed; the rule's qualifier gets moved to the WHERE of the INSERT...SELECT, and the way you get no effect is for the qual to fail on every row the SELECT generates.
One way to think about the problem (though I'm not sure this is right in detail) is that there's no place to hang a top-level WHERE on a UNION.
What you are saying makes sense to me (and I have already rewritten that rule, and it is working now)... but it's unfortunate that I had to spend half a day trying to figure out why the damn thing doesn't work... (even worse really - I've written that rule a while ago, and it already made it into the production database before anyone noticed that it did not really work) :-(
It would have saved a lot of trouble if it just complained about that union thing right away and refuse to create the rule...
On a different note, I think there *is* a way to add a where clause to the union - that's exactly what I did in that last example - by converting it into a subselect...
Can that not be done automatically for conditional rules?
(I doubt that would be very useful though... since it's no longer possible to use old and new there... I can't really think of any useful application of a union inside a rule, except for my obscure 'select 1,2' example :-)
Dima
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
