Re: user-defined not working - Mailing list pgsql-novice

Patrick,

> Hello.  I'm trying to create my first function. However, when I try
>  to
> compile, I get an error message:  Unrecognized language specified in
>  a
> CREATE FUNCTION: 'plpgsql'

Ah.  Go to the command line.  Switch to the postgres user (whoever owns
 the PostgreSQL directories).  Run:
createlang plpgsql template1

Additionally, if you have already created other databases, you need to
 run
createlang plpgsql database-name
for each database as the database owner (the user who created the
 database).

> '---Start function--
> CREATE Function OwnedGoods
>     ( varchar(6)
>      ,  varchar(2)

Don't designate VARCHAR sizes when using them as function variables.
  Just "VARCHAR".

>      ,   int
>      ,   int
>      ,  int
>      ,   int
>      ,   int
>      ,  int
>     ) RETURNS integer
> as '
>
> DECLARE
>        GMM_ID ALIAS FOR $1;
>        Div_ID ALIAS FOR $2;
>        Loc1 ALIAS FOR $3;
>        Loc2 ALIAS FOR $4;
>        Loc3 ALIAS FOR $5;
>        Loc120 ALIAS FOR $6;
>        Loc15 ALIAS FOR $7;
>        Cheshire ALIAS FOR $8;
>        Total Int

I highly reccommend that you consider changing your coding practices.
  I think that you will find in PL/pgSQL that mixed-case variable names
 are more trouble than they're worth.

>
> Begin
>
>     IF GMM_ID = ''0001'' THEN
>        IF Div_id =  ''15'' or Div_id =  ''16'' or  Div_id =  ''17''
>   or
> Div_id =  ''23'' THEN
>           Total = sum(Loc120 + Loc3 )
>        Else
>           Total = sum(Cheshire)
>        END IF;
>     END IF;
>     IF GMM_ID = ''0002'' THEN
>        Total = sum(loc15 + Loc3)
>     END IF;
>     IF GMM_ID = ''0006'' or GMM_ID = ''0007''  THEN
>        Total = sum(Loc1 + loc2 +Cheshire)
>     END IF;
>     IF GMM_ID = ''0008'' THEN
>        IF Div_id =  ''99'' THEN
>            Total = sum(Loc120 + Loc3 )
>        END IF;
>     END IF;
>     IF GMM_ID = ''0003'' or GMM_ID = ''0005''  or GMM_ID = ''0009''
>   or
> GMM_ID = ''999''  THEN
>        Total = sum(Cheshire)
>     END IF;
>     IF  GMM_ID is null or Div_id=''00'' THEN
>       Total = sum(Cheshire)
>     END IF;
>
>     Return Total;
> END;
> ' LANGUAGE 'plpgsql';

Hmmm... you've got a bunch of other syntax problems here (too many to
 list).  Have you read the full documentation for PL/pgSQL?   Also
 consult some Oracle PL/SQL books; PL/pgSQL was written to be
 syntax-compatible with PL/SQL.

> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office

Tell you what ... if we can boast online that Macy's uses PostgreSQL,
 you may be able to get some one-on-one online free support.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco