Re: RULES and QUALIFICATION for INSERT

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