Thread: Could someone tell me what is wrong withthis function

Could someone tell me what is wrong withthis function

From

Date:

25 March 2002, 12:37:51

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

Re: Could someone tell me what is wrong withthis function

From

Darren Ferguson

Date:

25 March 2002, 12:54:07

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
>

Re: Could someone tell me what is wrong withthis function

From

"PGMailList"

Date:

25 March 2002, 13:07:58

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
>

Re: Could someone tell me what is wrong withthis function

From

Date:

25 March 2002, 13:31:47

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

Re: Could someone tell me what is wrong withthis function

From

"PGMailList"

Date:

25 March 2002, 13:40:27

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
>

Re: Could someone tell me what is wrong withthis function

From

Darren Ferguson

Date:

25 March 2002, 13:49:07

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