X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.225.181])
	by svr1.postgresql.org (Postfix) with ESMTP id 99FB1CCA51
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 15:49:48 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new,
	port 10024) with ESMTP id 57091-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 15:50:04 +0000 (GMT)
Received: from stan.ringways.co.uk (hub.ringways.co.uk [213.38.87.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 58B55CCA08
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 12:49:33 -0300 (ADT)
Received: from gary.ringways.co.uk ([10.1.1.2])
	by stan.ringways.co.uk with esmtp (Exim 3.22 #1)
	id 19dB0F-0003Gk-00; Thu, 17 Jul 2003 16:49:07 +0100
Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Dmitry Tkach <dmitry@openratings.com>
Subject: Re: unique value - trigger?
Date: Thu, 17 Jul 2003 16:49:25 +0100
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
	<3F16B3EE.2060006@openratings.com>
In-Reply-To: <3F16B3EE.2060006@openratings.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307171649.25614.gary.stainburn@ringways.co.uk>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/228
X-Sequence-Number: 14342

On Thursday 17 Jul 2003 3:34 pm, Dmitry Tkach wrote:
> Gary Stainburn wrote:
> >Hi folks,
> >
> >I'm back with my lnumbers table again.
> >
> >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
> >
> >While each loco can have a number of different numbers, only one can be
> >current at any one time.
> >
> >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>
>
> Why "not"? I thought, you wanted just the opposite - update the ones
> that *do* have the same lnid?
> I'd also recommend you to add ' and lncurrent' to the query - otherwise
> every insert would be updating *every* row with the same lnid (it
> doesn't check if the new row is actually the same as the old one) before
> updating, and that may be expensive.
> You may also want to create a pratial index on lnumbers (lnid) where
> lncurrent to speed up your trigger

All good and valid points.

>
> >but I can't seem to sus it put.
>
> What is the problem?

The problem is I don't know how to convert the following pseudo code to valid 
SQL:

create trigger unique_current on insert/update to lnumbers
  if new.lncurrent = true
    update lnumbers set all other records for this loco to false


>
> Dima
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000