Re: 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(at)brutele(dot)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
>