Re: [GENERAL] 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 <dmitry(at)openratings(dot)com> 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
>
>