Tom Lane wrote:
>Dmitry Tkach <dmitry@openratings.com> writes:
>
>
>>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.
>
>
>
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?
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