Re: changing an update via rules - caught in recursion - Mailing list pgsql-general

Resolution for the archives:

I created a view essentially like:
CREATE VIEW editor_contact AS SELECT c.contact_id, c.password, (SELECT
csg.securitygroup_id FROM contactsecuritygroup csg WHERE
((csg.contact_id = c.contact_id) AND (csg.primarygroup = true)) ORDER BY
csg.securitygroup_id LIMIT 1) AS primarygroupname FROM contact c;

then a function to keep the unique primary group:
CREATE FUNCTION contactsecgrp_setprimarygrpname(varchar(255),varchar(255))
RETURNS bool
DECLARE
  new_contact_id ALIAS FOR $1;
  new_primarygroupname ALIAS FOR $2;
BEGIN
  DELETE from contactsecuritygroup WHERE contact_id=NEW_contact_id and securitygroup_id!=NEW_primarygroupname and
primarygroup=true;
  IF (SELECT count(contact_id) from contactsecuritygroup where securitygroup_id=NEW_primarygroupname and
contact_id=NEW_contact_id)=0
    THEN INSERT into contactsecuritygroup (contact_id, securitygroup_id, primarygroup) VALUES (NEW_contact_id,
NEW_primarygroupname,true); 
    ELSE UPDATE contactsecuritygroup set primarygroup=true WHERE contact_id=NEW_contact_id and
securitygroup_id=NEW_primarygroupname;
  END IF;
  RETURN true;
END;

then three rules to update the contact table and call the function to
update the grouping table:

CREATE RULE editor_contact_delete
AS ON DELETE TO editor_contact
DO INSTEAD
DELETE FROM contact WHERE (contact.contact_id = old.contact_id);

CREATE RULE editor_contact_insert
AS ON INSERT TO editor_contact
DO INSTEAD (
INSERT INTO contact (contact_id, password) VALUES (new.contact_id, new.password);
SELECT contactsecgrp_setprimarygrpname(new.contact_id, new.primarygroupname) AS contactsecgrp_setprimarygrpname;
);

CREATE RULE editor_contact_update
AS ON UPDATE TO editor_contact
DO INSTEAD (
UPDATE contact SET contact_id = new.contact_id, password = new.password WHERE (contact.contact_id = old.contact_id);
SELECT contactsecgrp_setprimarygrpname(new.contact_id, new.primarygroupname) AS contactsecgrp_setprimarygrpname;
);

A cascade on update/delete rule on the constraint between the contact
and group tables handles changes to contact_id, and removes group links
with their contact.

Peace,
Lauren.

On Wed, 2003-07-16 at 17:45, Tom Lane wrote:
> Lauren Matheson <lmatheson@ivcf.ca> writes:
> > CREATE RULE contactsecgrp_update_1pg_them
> > AS ON UPDATE TO contactsecuritygroup
> > WHERE NEW.primarygroup=true
> > DO
> > UPDATE contactsecuritygroup SET primarygroup=false WHERE contact_id =
> > NEW.contact_id and securitygroup_id != NEW.securitygroup_id;
>
> You can't do that, because a rule is a macro expanded during query
> preparation, and the above is going to lead to infinite recursion
> during macro expansion.  (No, the WHERE clause does not stop it,
> because the actual value of WHERE is only checked at run time.)
>
> You could make this sort of adjustment in a BEFORE UPDATE trigger,
> though.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match