Re: Could someone tell me what is wrong withthis function

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