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

On Tuesday 05 February 2002 04:32 pm, Patrick Hatcher wrote:


Some more stuff....

Here is the setup after the language addition (\dS to list system table names)

si@guinness > psql
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

nrgdb=# \dS
          List of relations
      Name      |  Type   |  Owner
----------------+---------+----------
 pg_aggregate   | table   | postgres
 pg_am          | table   | postgres
 pg_amop        | table   | postgres
 pg_amproc      | table   | postgres
 pg_attrdef     | table   | postgres
 pg_attribute   | table   | postgres
 pg_class       | table   | postgres
 pg_database    | table   | postgres
 pg_description | table   | postgres
 pg_group       | table   | postgres
 pg_index       | table   | postgres
 pg_indexes     | view    | postgres
 pg_inheritproc | table   | postgres
 pg_inherits    | table   | postgres
 pg_ipl         | table   | postgres
 pg_language    | table   | postgres
 pg_largeobject | table   | postgres
 pg_listener    | table   | postgres
 pg_log         | special | postgres
 pg_opclass     | table   | postgres
 pg_operator    | table   | postgres
 pg_proc        | table   | postgres
 pg_relcheck    | table   | postgres
 pg_rewrite     | table   | postgres
 pg_rules       | view    | postgres
 pg_shadow      | table   | postgres
 pg_statistic   | table   | postgres
 pg_tables      | view    | postgres
 pg_trigger     | table   | postgres
 pg_type        | table   | postgres
 pg_user        | view    | postgres
 pg_variable    | special | postgres
 pg_views       | view    | postgres
 pg_xactlock    | special | postgres
(34 rows)

nrgdb=# select * from pg_language;
 lanname  | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
 internal | f       | f            |             0 | n/a
 C        | f       | f            |             0 | /bin/cc
 sql      | f       | f            |             0 | postgres
 plpgsql  | t       | t            |     332485484 | PL/pgSQL
(4 rows)



> 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'
>
> Any help would be greatly appreciated:
>
> '---Start function--
> CREATE Function OwnedGoods
>     ( varchar(6)
>      ,  varchar(2)
>      ,   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
>
> 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';
>
> '----END FUNCTION
>
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster