Re: [BUGS] INSTEAD rule bug?

Aha!

I got it.
This generates the 'cannot insert null...' error:

create table test (x int not null, y int not null);
create table test_reject (x int, y int, reason text);

create view test_view as select * from test;

create rule reject_x as on insert to test_view where new.x is null do 
instead insert into test_reject values (new.*, 'NULL x');

create rule reject_y as on insert to test_view where new.y is null do 
instead insert into test_reject values (new.*, 'NULL y');

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.*;
);

create rule insert_dummy as on insert to test_view do instead nothing;

-- insert into test_reject values (new.*,
-- case when new.x is null then 'NULL x' else 'NULL y' end);

insert into test_view values (null, null);


It looks like the UNION in the 'not null' rule is the problem.
If I change it to just insert ... select (without the union), or to two 
inserts, then it works.
But union always fails, even if I add a 'where false' to the end, so 
that it only returns one row...

Dima


Tom Lane wrote:

>Dmitry Tkach  writes:
>  
>
>>But what the hell is my problem then??? I swear, I do insert into the 
>>view there :-)
>>It's a really huge view, looking at a whole bunch of different tables... 
>>I'd hate having to post the whole thing...
>>    
>>
>
>All I can guess is a bug (or pilot error) that's triggered by the more
>complex view.  I think you'll just have to try to whittle down the
>failure to something you can post.
>
>			regards, tom lane
>  
>