From: Richard Poole <richard@ruthie.org>
To: pgsql-sql@postgresql.org
Subject: Re: unique value - trigger?
Date: Thu, 17 Jul 2003 17:05:47 +0100
Message-ID: <20030717160547.GB1272@guests.deus.net> (raw)
In-Reply-To: <200307171256.52098.gary.stainburn@ringways.co.uk>
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
On Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote:
>
> nymr=# \d lnumbers
> Table "lnumbers"
> Column | Type | Modifiers
> -----------+-----------------------+-----------
> lnid | integer | not null
> lnumber | character varying(10) | not null
> lncurrent | boolean |
> Primary key: lnumbers_pkey
> Triggers: RI_ConstraintTrigger_7575462
>
> I want to make it so that if I set lncurrent to true for one row, any existing
> true rows are set to false.
>
> I'm guessing that I need to create a trigger to be actioned after an insert or
> update which would update set lncurrent=false where lnid not = <current lnid>
Absolutely. Something like this will work:
CREATE FUNCTION lnumbers_current_trigger() RETURNS TRIGGER AS '
BEGIN
IF NEW.lncurrent THEN
UPDATE lnumbers SET lncurrent = ''f''
WHERE lnid = NEW.lnid AND lnumber != NEW.lnumber AND lncurrent = ''t'';
END IF;
RETURN NEW;
END' LANGUAGE 'plpgsql';
CREATE TRIGGER lnumbers_current AFTER UPDATE OR INSERT ON lnumbers
FOR EACH ROW EXECUTE PROCEDURE lnumbers_current_trigger();
(Lightly tested only on 7.3.3)
In the WHERE condition in the function, specifying "lncurrent = 't'"
means that we don't update more rows than we have to. Making the
trigger fire AFTER INSERT as well as AFTER UPDATE means that if you
just add a new row with lncurrent set to true, it Does The Right
Thing. In this particular example, the trigger will work perfectly
well as a BEFORE, also.
If you can't or don't want to install PL/PgSQL (or some other
procedural language), you can do it with rules. It's more long-winded
that way, although I also think it's more elegant...
Richard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@ruthie.org
Subject: Re: unique value - trigger?
In-Reply-To: <20030717160547.GB1272@guests.deus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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