Cannot compile plpgsql function

[twinklyblue]

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. :-)

[michaelb]

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

select * from pg_language;

and post the results.

2) Post the function declaration, which you sourcing from an external file.

[twinklyblue]

select * from pg_language;

Here's the result for this step:

 lanname  | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |    lanacl   
----------+---------+--------------+---------------+--------------+---------------
 internal | f       | f            |             0 |         2246 |
 c        | f       | f            |             0 |         2247 |
 sql      | f       | t            |             0 |         2248 | {=U/postgres}
(3 rows)
 

Here's the code of the function I was trying to compile.

create or replace function upd_menu (varchar(50),real, varchar(50),varchar(100)) returns integer as
'
declare
       foodname alias for $1;
       foodprice alias for $2;
       fname alias for $3;
       fpath alias for $4;
       check_validity integer;
       id_photo integer;
 
begin
     update food 
     set price = foodprice
     where food = foodname;
 
     id_photo:= ''select id from tbl_photo a, menu b where a.id = b.photo_id and food=foodname'';
     if id_photo > 0 then
 
        update tbl_photo
        set filename = fname
        , filepath = fpath
        where id =  id_photo;
 
        select 1 as result;   
     else
         insert into tbl_photo (id, filename,filepath) values (select get_highest_id()+1, fname, fpath);
         select 1 as result;
     end if; 
 
 
end;
'
language plpgsql;
 

[michaelb]

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

[twinklyblue]

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:

create or replace function upd_menu (varchar(50),real, varchar(50),varchar(100)) returns integer as
'
declare
       foodname alias for $1;
       foodprice alias for $2;
       fname alias for $3;
       fpath alias for $4;
       check_validity integer;
       id_photo integer;
 
begin
     update menu 
     set price = foodprice
     where food = foodname;
 
   -- id_photo:= ''select id as id_photo from tbl_photo a, menu b where a.id = b.photo_id and food=foodname'';
     select id as id_photo from tbl_photo a, menu b where a.id = b.photo_id and food=foodname;
     if id_photo > 0 then
 
        update tbl_photo
        set filename = fname
        , filepath = fpath
        where id =  id_photo;
 
        return 1;   
     else
         insert into tbl_photo (id, filename,filepath) values (select get_highest_id()+1, fname, fpath);
        return 1;
     end if; 
 
 
end;
'
language 'plpgsql';
 

Here's what I executed:

select upd_menu('hamburger', 90, 'hamburger','upload/hamburgertransparent2kb.gif'); 
 

Thanks for your help!

[michaelb]

Hi, twinklyblue;

The error message is indeed not very helpful, let's try to replace

select id as id_photo from tbl_photo a, menu b ...
-- WITH
select id into id_photo from tbl_photo a, menu b ...
 

although I'm a bit surprised that this wasn't caught at compilation.

Let us know whether this helped.

[twinklyblue]

HI Mike,

Sorry for the late reply but apparently, I was able to debug my script. Thanks for your help as always!