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@openratings.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
>
>
