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