3) Oops not a rule but a check
ALTER TABLE "your_table"
ADD CONSTRAINT your_table_check CHECK (countrycode ~ '[A-Z]*');
http://www.pgexplorer.com
----- Original Message -----
From: "PGMailList" <pgmail@pgexplorer.com>
To: <tsmets@brutele.be>; <pgsql-general@postgresql.org>
Sent: Monday, March 25, 2002 5:06 PM
Subject: Re: [GENERAL] Could someone tell me what is wrong withthis function
> 1) as 'BEGIN
> as '
> BEGIN
>
> Put BEGIN in next line
>
> 2) if new.countrycode !~''^[A-Za-z][A-Za-z]$''
> wont check for only Uppercase Letters try
> ''^[A-Z]*''
>
> 3) You can also use a Rule on the field, but its nice to use a trigger if
> you require a better error to be reported
>
> http://www.pgexplorer.com
> GUI postgresql Frontend
>
>
>
>
>
> ----- Original Message -----
> From: <tsmets@brutele.be>
> To: <pgsql-general@postgresql.org>
> Sent: Monday, March 25, 2002 4:36 PM
> Subject: [GENERAL] Could someone tell me what is wrong withthis function
>
>
> >
> > This is almost a copy / paste from Bruce Momjian's book :
> > <snip>
> > create function trigger_iso_code_2_uppercase()
> > returns opaque
> > as 'BEGIN
> > if new.countrycode !~''^[A-Za-z][A-Za-z]$''
> > then raise exception ''ISO-code are excepted to be uppercase
> > letters.'';
> > ENd if;
> > new.countrycode=upper(new.countrycode);
> > new.countryname=initcap(new.countryname);
> > end;'
> > language 'internal';
> > </snip>
> >
> > but I get the following :
> >
> > <snip>
> > ERROR: ProcedureCreate: there is no builtin function named
"BEGIN
> > if new.countrycode !~'^[A-Za-z][A-Za-z]$'
> > then raise exception 'ISO-code are excepted to be uppercase
> > letters.';
> > ENd if;
> > new.countrycode=upper(new.countrycode);
> > new.countryname=initcap(new.countryname);
> > end;"
> > </snip>
> >
> > Please note that I logging as postgres
> >
> > tx for your help,
> >
> > thomas,
> >
> >
> >
> >
> >
> >
> > --
> > Thomas SMETS
> > rue J. Wytsmanstraat 62
> > 1050 Bruxelles
> > yahoo-id : smetsthomas
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
