RULES and QUALIFICATION for INSERT

From: srdjan <srdjan(dot)matic(at)anche(dot)no>
To: pgsql-general(at)postgresql(dot)org
Subject: RULES and QUALIFICATION for INSERT
Date: 2008-03-25 18:40:26
Message-ID: 47E9471A.7080509@anche.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Edward Blake 2008-03-25 18:51:05 Converting mysql "on update" to postgres "rule"
Previous Message Roberts, Jon 2008-03-25 18:37:37 Re: select any table