|
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@xxxxxxxxx','bill',3,5); INSERT INTO a VALUES ('2mail@xxxxxxxxx','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@xxxxxxxxx','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? |
