Message-ID: <3F16C8D6.1050603@openratings.com>
Date: Thu, 17 Jul 2003 12:03:34 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: unique value - trigger?
References: <200307171256.52098.gary.stainburn@ringways.co.uk>
	<3F16B3EE.2060006@openratings.com>
	<200307171649.25614.gary.stainburn@ringways.co.uk>
In-Reply-To: <200307171649.25614.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>
>
>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