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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
