Thread: RULES and QUALIFICATION for INSERT

Hi to everyone.
I'll try to explain my problem with an example.

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

If I've understood well, the qualification (WHERE NEW.name = 'tom') is the condition under which the rule has to be executed.
Only if the condition is met, the rule is executed.
I noticed that if I remove the qualification, the rule works, but doing so I am not able anymore to test the condition. (I could overcame this problem with a trigger, but I'd prefer if someone could explain me how to do this with rules).

Best regards
Srdjan Matic

srdjan wrote:
> I'll try to explain my problem with an example.
>
> -- 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));
>
> 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.
>
> If I've understood well, the qualification (WHERE NEW.name = 'tom') is the condition under which the rule has to be
executed. 
> Only if the condition is met, the rule is executed.
> I noticed that if I remove the qualification, the rule works, but doing so
> I am not able anymore to test the condition. (I could overcame this problem
> with a trigger, but I'd prefer if someone could explain me how to do this with rules).

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?

Yours,
Laurenz Albe

srdjan wrote:
>> 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?
>
> In this case you shouldn't be able to do this insert.

Should it generate an error message or do nothing?

Yours,
Laurenz Albe

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

Albe Laurenz wrote:

srdjan wrote: 

I'll try to explain my problem with an example. 

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

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. 

If I've understood well, the qualification (WHERE NEW.name = 'tom') is the condition under which the rule has to be executed. 
Only if the condition is met, the rule is executed. 
I noticed that if I remove the qualification, the rule works, but doing so
I am not able anymore to test the condition. (I could overcame this problem
with a trigger, but I'd prefer if someone could explain me how to do this with rules).    

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?

Yours,
Laurenz Albe 

In this case you shouldn't be able to do this insert.
Mine in only a example but in reality I've created the view for security reasons (the view uses function current_user and allows users to see only the rows that satisfy particular requirements).
The insert is invoked on the view, but in fact it works only on the first table.
The qualification should test if the NEW value inserted for a specific attribute is or not in a specific pool/range of allowed values [in my sample the only case which is allowed in when NEW.name = 'tom']

Regards
Srdjan Matic

Albe Laurenz wrote:

srdjan wrote: 

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?     

In this case you shouldn't be able to do this insert.   

Should it generate an error message or do nothing?

Yours,
Laurenz Albe 

It's not important, but maybe an error message would be preferred.

Regards,
Srdjan Matic