X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 33F60CCA49
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 16:05:21 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 89871-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 16:05:49 +0000 (GMT)
Received: from jalapeno.crazydogs.org (jalapeno.jellybean.co.uk
	[212.78.70.100])
	by svr1.postgresql.org (Postfix) with ESMTP id CB219CADD0
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 13:05:18 -0300 (ADT)
Received: from rp by jalapeno.crazydogs.org with local (Exim 3.35 #1 (Debian))
	id 19dBGO-0005sA-00
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 17:05:48 +0100
Date: Thu, 17 Jul 2003 17:05:47 +0100
From: Richard Poole <richard@ruthie.org>
To: pgsql-sql@postgresql.org
Subject: Re: unique value - trigger?
Message-ID: <20030717160547.GB1272@guests.deus.net>
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200307171256.52098.gary.stainburn@ringways.co.uk>
User-Agent: Mutt/1.3.28i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/279
X-Sequence-Number: 14393

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