Обсуждение: INFINITE RECURSION with rules...
Hi to everyone.
I've got some problem with rules.
-- I have 2 tables
CREATE TABLE a (email varchar(20), name varchar(10), num1 smallint, num2 smallint, PRIMARY KEY (email, name));
CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES a(email, name));
/*
My goal is to calculate and insert automatically the value of “tot” when I insert a row into table b.
*/
-- Some samples
INSERT INTO a VALUES ('mail1@email.com','bill',3,5);
INSERT INTO a VALUES ('2mail@email.com','paul',4,7);
-- Then I created a simple function
CREATE OR REPLACE FUNCTION calc(varchar(10), varchar(20)) RETURNS smallint AS $$
DECLARE
rowww a%ROWTYPE;
BEGIN
SELECT * INTO rowww FROM a WHERE email = $1 AND name = $2;
IF FOUND
THEN RETURN rowww.num1 * rowww.num2;
ELSE
RAISE WARNING 'Error: values non found!';
END IF;
END;
$$ LANGUAGE plpgsql;
-- And this easy rule
CREATE RULE rrr_a_b AS ON INSERT TO b
DO INSTEAD
INSERT INTO b VALUES
(NEW.id,
NEW.email_a,
NEW.name_a,
(SELECT calc(NEW.email_a, NEW.name_a))
);
-- Sample for insert into b
INSERT INTO b VALUES (33,'mail1@email.com','bill');
Trying to insert into b (and using the new rule defined by myself, i receive this message:
ERROR: infinite recursion detected in rules for relation "b"
How I could solve this problem?
On Sun, Mar 23, 2008 at 10:30 AM, srdjan <srdjan.matic@anche.no> wrote: [...] > CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a > varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES a(email, > name)); > [...] > > -- And this easy rule > > CREATE RULE rrr_a_b AS ON INSERT TO b > DO INSTEAD > INSERT INTO b VALUES > (NEW.id, > NEW.email_a, > NEW.name_a, > (SELECT calc(NEW.email_a, NEW.name_a)) > ); > > -- Sample for insert into b > > INSERT INTO b VALUES (33,'mail1@email.com','bill'); > [...] > Trying to insert into b (and using the new rule defined by myself, i receive > this message: > > ERROR: infinite recursion detected in rules for relation "b" > when you insert into b the rule rewrites your query into an insert into b... ah... another insert into b, the rule rewrites *again* the query into (guess what?) another insert into b... and the rule system will continue rewriting your query until it get something different to an insert into b... hope i was clear... now, why the rule? isn't enough to simply do this? INSERT INTO b VALUES (33,'mail1@email.com','bill', calc('mail1@email.com', 'bill')); or maybe using a trigger before insert but you're insert should look like: INSERT INTO b(id, email_a, name_a) VALUES (33,'mail1@email.com','bill'); and in the trigger fill the tot column -- regards, Jaime Casanova
srdjan wrote: > /* > *My goal is to calculate and insert automatically the value of "tot" > when I insert a row into table b.* > */ Use a trigger. It's a lot simpler. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support