Re: [GENERAL] INSTEAD rule bug?

Tom Lane wrote:

>Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
>
>
>>create rule insert_test as on insert to test_view where new.x is not
>>null and new.y is not null do instead
>>(
>> insert into test
>> select new.* union
>> select new.*;
>>);
>>
>>
>
>Mmm. In CVS tip that throws
>
>ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level
>
>
Actually, I just used that new.* as an example (if I understand this
error message correctly, that's what it refers to, right?)
Something like
insert into test
select null,null union select 1,2 where false

has the same problem... and it doesn't refer to any relations.

>which was a check added as a result of this discussion thread:
>http://archives.postgresql.org/pgsql-general/2003-02/msg00693.php
>
>
I'll take a look at that thread, thanks!

>I am sure you are running into some misbehavior associated with the
>fact that the rule transformation generates a bogusly-structured SQL
>query, and 7.2 isn't noticing.
>
>
Not just 7.2... I was testing this in 7.3 - it has the same problem

Dima

>I'd like to support this case someday, but it's not clear how...
>
>
>
I don't know if it helps, but somehow if I do

insert into test select * from (select null,null union select 1,2 where
false) as dummy

... that works fine.

Thanks!

Dima