From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Dmitry Tkach <dmitry@openratings.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: unique value - trigger?
Date: Thu, 17 Jul 2003 16:49:25 +0100
Message-ID: <200307171649.25614.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <3F16B3EE.2060006@openratings.com>
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
<3F16B3EE.2060006@openratings.com>
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
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: gary.stainburn@ringways.co.uk, dmitry@openratings.com
Subject: Re: unique value - trigger?
In-Reply-To: <200307171649.25614.gary.stainburn@ringways.co.uk>
* 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