RULES and QUALIFICATION for INSERT

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

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

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

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

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