Thread: language "plpgsql" does not exist
Hi everybody.
I'm a new born in the PostGreSQL domain (usually deal with Oracle or SQL
Server) and I got the following error when trying to create a function using
the default language plpgsql: language "plpgsql" does not exist
I was not able to find clues on the net to help me to solve this problem,
that's why I'm sending this post now.
I just installed the version 7.3.4 of PostGreSQL (basic installation).
Here the source of the function I try to create:
-------------
CREATE OR REPLACE FUNCTION ts_groupEdit
(VARCHAR,INTEGER,VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
DECLARE
vi_name ALIAS FOR $1;
vi_companyId ALIAS FOR $2;
vi_description ALIAS FOR $3;
vi_rights ALIAS FOR $4;
vi_debug ALIAS FOR $5;
v_groupId INTEGER;
BEGIN
SELECT groupId INTO v_groupId
FROM Groups
WHERE companyId=vi_companyid AND
name=vi_name;
IF v_groupId IS NOT NULL THEN
UPDATE GROUPS SET description=vi_description
WHERE groupId=v_groupId;
ELSE
INSERT INTO Groups (companyId, name, description)
VALUES (vi_companyid, vi_name, vi_description);
END IF;
RETURN 0;
END; '
LANGUAGE 'plpgsql';
-------------
Any help will be welcome.
May be I have to load or install the plpgsql module, but I didn't find such
instruction in the documentation so I guess it is there by default.
Seb.
--
Sébastien BAUDRY - DBA Oracle/MS-SQL & Project Manager
BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8.
@.: sebastien.baudry@baudry-engineering.com
mobile.: +36-30-392-0279 / tel: +36-1-326-0241 / fax: +36-1-326-0241
http://www.baudry-engineering.com
Hum, I feel sorry to have disturbed you with such beginner's question. I just found out the solution: should create the language entry myself. Next time I hope to have a more interesting question :-) Seb. -- Sébastien BAUDRY - DBA Oracle/MS-SQL & Project Manager BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8. @.: sebastien.baudry@baudry-engineering.com mobile.: +36-30-392-0279 / tel: +36-1-326-0241 / fax: +36-1-326-0241 http://www.baudry-engineering.com ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
createlang plpgsql <db> On Tue, 2 Sep 2003, Sebastien Baudry wrote: > Hi everybody. > I'm a new born in the PostGreSQL domain (usually deal with Oracle or SQL > Server) and I got the following error when trying to create a function using > the default language plpgsql: language "plpgsql" does not exist > I was not able to find clues on the net to help me to solve this problem, > that's why I'm sending this post now. > > I just installed the version 7.3.4 of PostGreSQL (basic installation). > > Here the source of the function I try to create: > ------------- > CREATE OR REPLACE FUNCTION ts_groupEdit > (VARCHAR,INTEGER,VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS ' > > DECLARE > vi_name ALIAS FOR $1; > vi_companyId ALIAS FOR $2; > vi_description ALIAS FOR $3; > vi_rights ALIAS FOR $4; > vi_debug ALIAS FOR $5; > v_groupId INTEGER; > BEGIN > SELECT groupId INTO v_groupId > FROM Groups > WHERE companyId=vi_companyid AND > name=vi_name; > > IF v_groupId IS NOT NULL THEN > UPDATE GROUPS SET description=vi_description > WHERE groupId=v_groupId; > ELSE > INSERT INTO Groups (companyId, name, description) > VALUES (vi_companyid, vi_name, vi_description); > END IF; > > RETURN 0; > END; ' > LANGUAGE 'plpgsql'; > ------------- > > > Any help will be welcome. > May be I have to load or install the plpgsql module, but I didn't find such > instruction in the documentation so I guess it is there by default. > > Seb. > > -- > Sébastien BAUDRY - DBA Oracle/MS-SQL & Project Manager > BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8. > @.: sebastien.baudry@baudry-engineering.com > mobile.: +36-30-392-0279 / tel: +36-1-326-0241 / fax: +36-1-326-0241 > http://www.baudry-engineering.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
You have to install the procedural language into each database as such: 1. su - postgres 2. createlang plpgsql [your dbname here] Sebastien Baudry wrote: >Hi everybody. >I'm a new born in the PostGreSQL domain (usually deal with Oracle or SQL >Server) and I got the following error when trying to create a function using >the default language plpgsql: language "plpgsql" does not exist >I was not able to find clues on the net to help me to solve this problem, >that's why I'm sending this post now. > >I just installed the version 7.3.4 of PostGreSQL (basic installation). > >Here the source of the function I try to create: >------------- >CREATE OR REPLACE FUNCTION ts_groupEdit >(VARCHAR,INTEGER,VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS ' > >DECLARE >vi_name ALIAS FOR $1; >vi_companyId ALIAS FOR $2; >vi_description ALIAS FOR $3; >vi_rights ALIAS FOR $4; >vi_debug ALIAS FOR $5; >v_groupId INTEGER; >BEGIN > SELECT groupId INTO v_groupId > FROM Groups > WHERE companyId=vi_companyid AND > name=vi_name; > > IF v_groupId IS NOT NULL THEN > UPDATE GROUPS SET description=vi_description > WHERE groupId=v_groupId; > ELSE > INSERT INTO Groups (companyId, name, description) > VALUES (vi_companyid, vi_name, vi_description); > END IF; > > RETURN 0; >END; ' >LANGUAGE 'plpgsql'; >------------- > > >Any help will be welcome. >May be I have to load or install the plpgsql module, but I didn't find such >instruction in the documentation so I guess it is there by default. > >Seb. > >-- >Sébastien BAUDRY - DBA Oracle/MS-SQL & Project Manager >BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8. >@.: sebastien.baudry@baudry-engineering.com >mobile.: +36-30-392-0279 / tel: +36-1-326-0241 / fax: +36-1-326-0241 >http://www.baudry-engineering.com > > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > >
And if you do the below with template1 - It will be available in every database you create after that . > You have to install the procedural language into each database as such: > > 1. su - postgres > 2. createlang plpgsql [your dbname here] ----- Original Message ----- From: "Renney Thomas" <renneyt@yahoo.com> To: <sebastien.baudry@baudry-engineering.com> Cc: "Admin Postgres" <pgsql-admin@postgresql.org> Sent: Tuesday, September 02, 2003 3:12 AM Subject: Re: [ADMIN] language "plpgsql" does not exist > You have to install the procedural language into each database as such: > > 1. su - postgres > 2. createlang plpgsql [your dbname here] > > > > Sebastien Baudry wrote: > > >Hi everybody. > >I'm a new born in the PostGreSQL domain (usually deal with Oracle or SQL > >Server) and I got the following error when trying to create a function using > >the default language plpgsql: language "plpgsql" does not exist > >I was not able to find clues on the net to help me to solve this problem, > >that's why I'm sending this post now. > > > >I just installed the version 7.3.4 of PostGreSQL (basic installation). > > > >Here the source of the function I try to create: > >------------- > >CREATE OR REPLACE FUNCTION ts_groupEdit > >(VARCHAR,INTEGER,VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS ' > > > >DECLARE > >vi_name ALIAS FOR $1; > >vi_companyId ALIAS FOR $2; > >vi_description ALIAS FOR $3; > >vi_rights ALIAS FOR $4; > >vi_debug ALIAS FOR $5; > >v_groupId INTEGER; > >BEGIN > > SELECT groupId INTO v_groupId > > FROM Groups > > WHERE companyId=vi_companyid AND > > name=vi_name; > > > > IF v_groupId IS NOT NULL THEN > > UPDATE GROUPS SET description=vi_description > > WHERE groupId=v_groupId; > > ELSE > > INSERT INTO Groups (companyId, name, description) > > VALUES (vi_companyid, vi_name, vi_description); > > END IF; > > > > RETURN 0; > >END; ' > >LANGUAGE 'plpgsql'; > >------------- > > > > > >Any help will be welcome. > >May be I have to load or install the plpgsql module, but I didn't find such > >instruction in the documentation so I guess it is there by default. > > > >Seb. > > > >-- > >Sébastien BAUDRY - DBA Oracle/MS-SQL & Project Manager > >BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8. > >@.: sebastien.baudry@baudry-engineering.com > >mobile.: +36-30-392-0279 / tel: +36-1-326-0241 / fax: +36-1-326-0241 > >http://www.baudry-engineering.com > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 7: don't forget to increase your free space map settings > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > >
Renney Thomas wrote: > You have to install the procedural language into each database as such: > > 1. su - postgres > 2. createlang plpgsql [your dbname here] > I still think we should display a 'hint' that createlang might fix the problem when we report a problem with a missing language --- these reports are just too common, and too easy to fix with a hint. --------------------------------------------------------------------------- > > > Sebastien Baudry wrote: > > >Hi everybody. > >I'm a new born in the PostGreSQL domain (usually deal with Oracle or SQL > >Server) and I got the following error when trying to create a function using > >the default language plpgsql: language "plpgsql" does not exist > >I was not able to find clues on the net to help me to solve this problem, > >that's why I'm sending this post now. > > > >I just installed the version 7.3.4 of PostGreSQL (basic installation). > > > >Here the source of the function I try to create: > >------------- > >CREATE OR REPLACE FUNCTION ts_groupEdit > >(VARCHAR,INTEGER,VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS ' > > > >DECLARE > >vi_name ALIAS FOR $1; > >vi_companyId ALIAS FOR $2; > >vi_description ALIAS FOR $3; > >vi_rights ALIAS FOR $4; > >vi_debug ALIAS FOR $5; > >v_groupId INTEGER; > >BEGIN > > SELECT groupId INTO v_groupId > > FROM Groups > > WHERE companyId=vi_companyid AND > > name=vi_name; > > > > IF v_groupId IS NOT NULL THEN > > UPDATE GROUPS SET description=vi_description > > WHERE groupId=v_groupId; > > ELSE > > INSERT INTO Groups (companyId, name, description) > > VALUES (vi_companyid, vi_name, vi_description); > > END IF; > > > > RETURN 0; > >END; ' > >LANGUAGE 'plpgsql'; > >------------- > > > > > >Any help will be welcome. > >May be I have to load or install the plpgsql module, but I didn't find such > >instruction in the documentation so I guess it is there by default. > > > >Seb. > > > >-- > >S?bastien BAUDRY - DBA Oracle/MS-SQL & Project Manager > >BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8. > >@.: sebastien.baudry@baudry-engineering.com > >mobile.: +36-30-392-0279 / tel: +36-1-326-0241 / fax: +36-1-326-0241 > >http://www.baudry-engineering.com > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 7: don't forget to increase your free space map settings > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073