Re: changing an update via rules - caught in recursion

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