INFINITE RECURSION with rules...

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?

Responses

Browse pgsql-general by date

  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