By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

Cannot compile plpgsql function

P: 37
Hi team,

I'm new in Postgres and I tried creating a stored procedure which performs numerous update procedure. As I compiled it, I was given the following error:

psql:storedproc.sql:33: ERROR: language "plpgsql" does not exist
HINT: You need to use "createlang" to load the language into the database.

So I tried to execute this code but nothing was returned:

>createlang plpgsql workdb

Thinking that everything was okay (since no error or confirmation was returned), I begin to recompile my function.

workdb-# \i storeproc.sql
storeproc.sql: そのようなファイルやディレクトリはありません

But was given that weird msgs. I am using a Japanese OS and I am not sure if this is related to it. To anyone who knows about this problem, your help would be greatly appreciated. :-)
Jun 13 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 534
I cannot decipher the error message, but there are two things you can do to help with debugging:

1) Run command createlang workdb -l
or even better, run this query
Expand|Select|Wrap|Line Numbers
  1. select * from pg_language;
and post the results.

2) Post the function declaration, which you sourcing from an external file.
Jun 14 '07 #2

P: 37
Expand|Select|Wrap|Line Numbers
  1. select * from pg_language;
Here's the result for this step:
Expand|Select|Wrap|Line Numbers
  1.  lanname  | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |    lanacl   
  2. ----------+---------+--------------+---------------+--------------+---------------
  3.  internal | f       | f            |             0 |         2246 |
  4.  c        | f       | f            |             0 |         2247 |
  5.  sql      | f       | t            |             0 |         2248 | {=U/postgres}
  6. (3 rows)
  7.  
Here's the code of the function I was trying to compile.
Expand|Select|Wrap|Line Numbers
  1. create or replace function upd_menu (varchar(50),real, varchar(50),varchar(100)) returns integer as
  2. '
  3. declare
  4.        foodname alias for $1;
  5.        foodprice alias for $2;
  6.        fname alias for $3;
  7.        fpath alias for $4;
  8.        check_validity integer;
  9.        id_photo integer;
  10.  
  11. begin
  12.      update food 
  13.      set price = foodprice
  14.      where food = foodname;
  15.  
  16.      id_photo:= ''select id from tbl_photo a, menu b where a.id = b.photo_id and food=foodname'';
  17.      if id_photo > 0 then
  18.  
  19.         update tbl_photo
  20.         set filename = fname
  21.         , filepath = fpath
  22.         where id =  id_photo;
  23.  
  24.         select 1 as result;   
  25.      else
  26.          insert into tbl_photo (id, filename,filepath) values (select get_highest_id()+1, fname, fpath);
  27.          select 1 as result;
  28.      end if; 
  29.  
  30.  
  31. end;
  32. '
  33. language plpgsql;
  34.  
Jun 19 '07 #3

Expert 100+
P: 534
As you can see there's no plpgsql language in your database.
The function definition may have its own issues, but you need to create the language first.

I am not sure what went wrong, did you ran this query on the same database where you tried to create language?
It may be helpful to review these man pages and try again.
If you succeed createlang -l or the query you ran should show plpgsql.

http://www.postgresql.org/docs/8.1/static/xplang.html
http://www.postgresql.org/docs/8.1/s...reatelang.html
http://www.postgresql.org/docs/8.1/s...elanguage.html
Jun 20 '07 #4

P: 37
As you can see there's no plpgsql language in your database.
The function definition may have its own issues, but you need to create the language first.

I am not sure what went wrong, did you ran this query on the same database where you tried to create language?
It may be helpful to review these man pages and try again.
If you succeed createlang -l or the query you ran should show plpgsql.

http://www.postgresql.org/docs/8.1/static/xplang.html
http://www.postgresql.org/docs/8.1/s...reatelang.html
http://www.postgresql.org/docs/8.1/s...elanguage.html

Hi Mike, The language apparently was installed by someone (i am not sure who but the last time i check it..it was already there) so I tried to recompile the stored proc again. It was successfully recompiled however, there is a runtime error which I cant understand. (postgreSQL's error display is really not that friendly, IMHO).

error:
ERROR: syntax error at or near "$1" at character 16
CONTEXT: PL/pgSQL function "upd_menu" line 15 at SQL statement


my script:

Expand|Select|Wrap|Line Numbers
  1. create or replace function upd_menu (varchar(50),real, varchar(50),varchar(100)) returns integer as
  2. '
  3. declare
  4.        foodname alias for $1;
  5.        foodprice alias for $2;
  6.        fname alias for $3;
  7.        fpath alias for $4;
  8.        check_validity integer;
  9.        id_photo integer;
  10.  
  11. begin
  12.      update menu 
  13.      set price = foodprice
  14.      where food = foodname;
  15.  
  16.    -- id_photo:= ''select id as id_photo from tbl_photo a, menu b where a.id = b.photo_id and food=foodname'';
  17.      select id as id_photo from tbl_photo a, menu b where a.id = b.photo_id and food=foodname;
  18.      if id_photo > 0 then
  19.  
  20.         update tbl_photo
  21.         set filename = fname
  22.         , filepath = fpath
  23.         where id =  id_photo;
  24.  
  25.         return 1;   
  26.      else
  27.          insert into tbl_photo (id, filename,filepath) values (select get_highest_id()+1, fname, fpath);
  28.         return 1;
  29.      end if; 
  30.  
  31.  
  32. end;
  33. '
  34. language 'plpgsql';
  35.  
Here's what I executed:

Expand|Select|Wrap|Line Numbers
  1. select upd_menu('hamburger', 90, 'hamburger','upload/hamburgertransparent2kb.gif'); 
  2.  
Thanks for your help!
Jun 21 '07 #5

Expert 100+
P: 534
Hi, twinklyblue;

The error message is indeed not very helpful, let's try to replace
Expand|Select|Wrap|Line Numbers
  1. select id as id_photo from tbl_photo a, menu b ...
  2. -- WITH
  3. select id into id_photo from tbl_photo a, menu b ...
  4.  
although I'm a bit surprised that this wasn't caught at compilation.

Let us know whether this helped.
Jun 22 '07 #6

P: 37
HI Mike,

Sorry for the late reply but apparently, I was able to debug my script. Thanks for your help as always!
Jul 10 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.