pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedchanging an update via rules
2+ messages / 2 participants
[nested] [flat]
* changing an update via rules
@ 2003-07-15 22:13 Lauren Matheson <lmatheson@ivcf.ca>
2003-07-20 06:15 ` Re: changing an update via rules Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Lauren Matheson @ 2003-07-15 22:13 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am having difficulty setting an on update rule which seems to be
caught in a recursive loop.
Context is a table with three columns assigning users to groups with the
third column being boolean to flag the primary group. I would like to
set an update rule to enforce one primary group. Any suggestions on how
to do this, or exactly how the code is getting trapped? My code is
below:
Thank you,
Lauren Matheson.
--change all others primarygroup to false when ours is true
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;
--change our primarygroup to true when no true exists and we were false
CREATE RULE contactsecgrp_update_1pg_us
AS ON UPDATE TO contactsecuritygroup
WHERE NEW.primarygroup=false and (select count(contact_id) from
contactsecuritygroup where contact_id=NEW.contact_id AND
primarygroup=true)=0
DO
UPDATE contactsecuritygroup SET primarygroup=true WHERE contact_id =
NEW.contact_id and securitygroup_id = NEW.securitygroup_id;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: changing an update via rules
2003-07-15 22:13 changing an update via rules Lauren Matheson <lmatheson@ivcf.ca>
@ 2003-07-20 06:15 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2003-07-20 06:15 UTC (permalink / raw)
To: Lauren Matheson <lmatheson@ivcf.ca>; +Cc: pgsql-sql
On 15 Jul 2003, Lauren Matheson wrote:
> Hello,
>
> I am having difficulty setting an on update rule which seems to be
> caught in a recursive loop.
>
> Context is a table with three columns assigning users to groups with the
> third column being boolean to flag the primary group. I would like to
> set an update rule to enforce one primary group. Any suggestions on how
> to do this, or exactly how the code is getting trapped? My code is
Rules are like macro rewrites and the WHERE clauses will not help to break
the recursive loop. I think you may need to either use a trigger or
something like a view so that the actual action happens on a different
table.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-20 06:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-15 22:13 changing an update via rules Lauren Matheson <lmatheson@ivcf.ca>
2003-07-20 06:15 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox