Thread: user-defined not working
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
On Tuesday 05 February 2002 04:32 pm, Patrick Hatcher wrote:
> 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:
>
Hi Patrick,
I think we all get caught out on this one :)
Have you run 'createlang' ?
Try:
createlang -U ${PGUSER} plpgsql ${PGDATABASE}
or
createlang plpgsql
or whatever enviroment variant makes sense for your setup
steve
> '---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
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
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