Cannot compile plpgsql function | Member | | Join Date: Jun 2007
Posts: 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. :-)
| | Moderator | | Join Date: Nov 2006 Location: Boston, USA
Posts: 505
| | | re: Cannot compile plpgsql function
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.
| | Member | | Join Date: Jun 2007
Posts: 37
| | | re: Cannot compile plpgsql function
- 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;
-
| | Member | | Join Date: Jun 2007
Posts: 37
| | | re: Cannot compile plpgsql function
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!
| | Moderator | | Join Date: Nov 2006 Location: Boston, USA
Posts: 505
| | | re: Cannot compile plpgsql function
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.
| | Member | | Join Date: Jun 2007
Posts: 37
| | | re: Cannot compile plpgsql function
HI Mike,
Sorry for the late reply but apparently, I was able to debug my script. Thanks for your help as always!
|  |
Similar PostgreSQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,327 network members.
|
