Re: INFINITE RECURSION with rules...

On Sun, Mar 23, 2008 at 10:30 AM, srdjan <srdjan(dot)matic(at)anche(dot)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(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"
>

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(at)email(dot)com','bill',
calc('mail1(at)email(dot)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(at)email(dot)com','bill');

and in the trigger fill the tot column

--
regards,
Jaime Casanova