Обсуждение: 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
Your language internal i have never heard of that.
Try langauge plpgsql this is what you seem to be writing in
Also the function says RETURNS OPAQUE but it does not return anything
Put just before END:- RETURN NEW;
Also the = should be := in plpgsql anyway. An example is below
HTH
Darren Ferguson
EXAMPLE
CREATE OR REPLACE FUNCTION trigger_iso_code_2_uppercase() RETURNS OPAQUE
AS '
BEGIN
IF NEW.countrycode !~ ''^[A-Za-z][A-Za-z]$'' THEN
RAISE EXCEPTION ''The exception'';
END IF;
NEW.countrycode := upper(NEW.countrycode);
NEW.countryname := initcap(NEW.countryname);
RETURN NEW;
END;' LANGUAGE 'plpgsql';
On Mon, 25 Mar 2002 tsmets@brutele.be wrote:
>
> 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
>
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 >
I created the function OKay, now !
It however complains still when I try to use the :
"create or replace"
instead of the "create function"
any idea ?
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas
----- Original Message -----
From: "Darren Ferguson" <darren@crystalballinc.com>
To: <tsmets@brutele.be>
Cc: <pgsql-general@postgresql.org>
Sent: 25 March, 2002 4:15 PM
Subject: Re: [GENERAL] Could someone tell me what is wrong withthis function
> Your language internal i have never heard of that.
>
> Try langauge plpgsql this is what you seem to be writing in
>
> Also the function says RETURNS OPAQUE but it does not return anything
>
> Put just before END:- RETURN NEW;
>
> Also the = should be := in plpgsql anyway. An example is below
>
> HTH
>
> Darren Ferguson
>
> EXAMPLE
>
> CREATE OR REPLACE FUNCTION trigger_iso_code_2_uppercase() RETURNS OPAQUE
> AS '
> BEGIN
>
> IF NEW.countrycode !~ ''^[A-Za-z][A-Za-z]$'' THEN
> RAISE EXCEPTION ''The exception'';
> END IF;
>
> NEW.countrycode := upper(NEW.countrycode);
> NEW.countryname := initcap(NEW.countryname);
>
> RETURN NEW;
> END;' LANGUAGE 'plpgsql';
>
>
> On Mon, 25 Mar 2002 tsmets@brutele.be wrote:
>
> >
> > 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
> >
>
>
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 >
Yep your version of postgres does not support the create or replace. It came out in 7.2 i believe. Just use CREATE FUNCTION instead of CREATE OR REPLACE. Darren Ferguson On Mon, 25 Mar 2002 tsmets@brutele.be wrote: > I created the function OKay, now ! > It however complains still when I try to use the : > "create or replace" > instead of the "create function" > > any idea ? > > thomas, > > -- > Thomas SMETS > rue J. Wytsmanstraat 62 > 1050 Bruxelles > yahoo-id : smetsthomas > ----- Original Message ----- > From: "Darren Ferguson" <darren@crystalballinc.com> > To: <tsmets@brutele.be> > Cc: <pgsql-general@postgresql.org> > Sent: 25 March, 2002 4:15 PM > Subject: Re: [GENERAL] Could someone tell me what is wrong withthis function > > > > Your language internal i have never heard of that. > > > > Try langauge plpgsql this is what you seem to be writing in > > > > Also the function says RETURNS OPAQUE but it does not return anything > > > > Put just before END:- RETURN NEW; > > > > Also the = should be := in plpgsql anyway. An example is below > > > > HTH > > > > Darren Ferguson > > > > EXAMPLE > > > > CREATE OR REPLACE FUNCTION trigger_iso_code_2_uppercase() RETURNS OPAQUE > > AS ' > > BEGIN > > > > IF NEW.countrycode !~ ''^[A-Za-z][A-Za-z]$'' THEN > > RAISE EXCEPTION ''The exception''; > > END IF; > > > > NEW.countrycode := upper(NEW.countrycode); > > NEW.countryname := initcap(NEW.countryname); > > > > RETURN NEW; > > END;' LANGUAGE 'plpgsql'; > > > > > > On Mon, 25 Mar 2002 tsmets@brutele.be wrote: > > > > > > > > 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 > > > > > > > > >