Re: RULES and QUALIFICATION for INSERT

srdjan wrote:
>>> -- I've got 2 tables and one view
>>> CREATE TABLE a (name varchar(20) primary key, num integer);
>>> CREATE TABLE b (town varchar(15), name varchar(20) references a(name));
>>>
>>> insert into a values ('tom',5);
>>> insert into a values ('paul',99);
>>> insert into a values ('jack',1234);
>>> insert into b values ('london','tom');
>>> insert into b values ('rome','paul');
>>>
>>> CREATE VIEW vvv AS SELECT * FROM a NATURAL JOIN b;
>>>
>>> -- I've crated a rule in this way
>>>
>>> CREATE RULE rrr AS ON INSERT TO vvv
>>> WHERE NEW.name = 'tom'
>>> DO INSTEAD
>>> INSERT INTO a VALUES (NEW.name, NEW.num);
>>>
>>>
>>> Trying a simple INSERT INTO vvv, I receive this message:
>>> ERROR: cannot insert into a view
>>> HINT: You need an unconditional ON INSERT DO INSTEAD rule.
>>
>> What is the desired response to
>>
>> INSERT INTO vvv (name, town, num) VALUES ('Karl', 'Leipzig', 18);
>>
>> Should this generate an error message, do nothing, or insert something?
>
> It's not important, but maybe an error message would be preferred.

You'll have to create a "dummy" unconditional DO INSTEAD rule,
as explained in
http://www.postgresql.org/docs/current/static/sql-createrule.html

The 'do nothing' case is simpler:

CREATE RULE vvv_dummy AS
ON INSERT TO vvv
DO INSTEAD NOTHING;
CREATE RULE vvv_ins AS
ON INSERT TO vvv WHERE NEW.name = 'tom'
DO INSTEAD INSERT INTO a VALUES (NEW.name, NEW.num);

If you want error messages if NEW.name is not 'tom', add a third rule:

CREATE RULE vvv_err AS
ON INSERT TO vvv WHERE NEW.name != 'tom' OR NEW.name IS NULL
DO INSTEAD SELECT 0/0;

Use something else than "SELECT 0/0" if you want a more intelligent error message.

Yours,
Laurenz Albe