pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunique value - trigger?
6+ messages / 4 participants
[nested] [flat]
* unique value - trigger?
@ 2003-07-17 11:56 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 3 replies; 6+ messages in thread
From: Gary Stainburn @ 2003-07-17 11:56 UTC (permalink / raw)
To: pgsql-sql
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>
but I can't seem to sus it put.
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique value - trigger?
@ 2003-07-17 14:34 Dmitry Tkach <dmitry@openratings.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 1 reply; 6+ messages in thread
From: Dmitry Tkach @ 2003-07-17 14:34 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
>but I can't seem to sus it put.
>
>
What is the problem?
Dima
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique value - trigger?
@ 2003-07-17 15:49 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gary Stainburn @ 2003-07-17 15:49 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique value - trigger?
@ 2003-07-17 16:03 Dmitry Tkach <dmitry@openratings.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Dmitry Tkach @ 2003-07-17 16:03 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
>
>
>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
>
>
>
I see... The bad news is you can't do it directly... You can only create
functions to run as triggers, not plain sql statements for some reason :-(
The correct syntax is
create trigger unique_current before insert or update on lnumbers for
each row execute procedure reset_current_lnum();
Where reset_current_lnum () is a function, that you have to write either
in "C" or in 'plpgsql';
I could give you some sample code in "C" to do that, but it's rather
complicated if you are not used to writing postgres stored procs in C...
plpgsql would be much easier, but I can't help you there, because I
don't know the syntax ... something like this, I guess, but I doubt this
will compile exactly as it is:
create function reset_current_lnum () returns triggers as
'
begin
if new.lncurrent = true
update lnumbers set lncurrent=false where lnid=new.lnid and
lncurrent;
endif
return new;
end;' language 'plpgsql';
Dima
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique value - trigger?
@ 2003-07-17 16:05 Richard Poole <richard@ruthie.org>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Poole @ 2003-07-17 16:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: unique value - trigger?
@ 2003-07-17 16:09 Richard Poole <rp@guests.deus.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Poole @ 2003-07-17 16:09 UTC (permalink / raw)
To: pgsql-sql
n 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-07-17 16:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-17 11:56 unique value - trigger? Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-17 14:34 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-17 15:49 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-17 16:03 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-17 16:05 ` Richard Poole <richard@ruthie.org>
2003-07-17 16:09 ` Richard Poole <rp@guests.deus.net>
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