From: | srdjan <srdjan(dot)matic(at)anche(dot)no> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | INFINITE RECURSION with rules... |
Date: | 2008-03-23 15:30:22 |
Message-ID: | 47E6778E.907@anche.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)email(dot)com
<mailto:%27mail1(at)email(dot)com>','bill',3,5);
INSERT INTO a VALUES ('2mail(at)email(dot)com
<mailto:%272mail(at)email(dot)com>','paul',4,7); <mailto:%27mail1(at)email(dot)com>
-- 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(at)email(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Clark | 2008-03-23 15:48:21 | Re: --enable-thread-safety bug |
Previous Message | Martijn van Oosterhout | 2008-03-23 14:51:21 | Re: How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc |