Re: [GENERAL] [BUGS] INSTEAD rule bug?

[Dmitry Tkach]

Tom Lane wrote:

Dmitry Tkach <[EMAIL PROTECTED]> 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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org