Re: Stored procedures..PLEASE HELP

Minal A. Aryamane wrote:
> hello all,
> I desparately need help in developing stored procedure using postgresql 8.0. I am using pgadmin for developing the stored procs. Can anyone please help me, The docs are really not helpful.
>
> am trying to pass values through a jsp page wherein I am accessing this stored procedures by the following select ststemet
>
> SELECT sp_build_course('ETLCRC0001:ETLCRC0010:', 'CRCSUB0001:CRCSUB0001:', 'ABC', 'minal' )
>
> the function is as below:
>
> CREATE OR REPLACE FUNCTION sp_build_course("varchar", "varchar", "varchar", "varchar")
> RETURNS text AS
[snip]
> SET COURSEIDS1:=COURSEIDS;
> SET TOPICIDS1:=TOPICIDS;
> SET COURSENAME1:=COURSENAME;
> SET USERNAME1:=USERNAME;

Umm - where do you get "SET" from? It's not in any of the manuals.

> --PRINT \'INSIDE THE STORED PROCEDURE\'

Or for that matter "PRINT"
[snip]

> when I run it I get the following error:
> ERROR: syntax error at or near "$1" at character 6
> CONTEXT: PL/pgSQL function "sp_build_course" line 45 at SQL statement.

From psql I get the following...

richardh=> \i fn_syntax_err.sql
CREATE FUNCTION
richardh=>
richardh=> SELECT sp_build_course('a','b','c','d');
ERROR: syntax error at or near "$1" at character 6
QUERY: SET $1 := $2
CONTEXT: PL/pgSQL function "sp_build_course" line 45 at SQL statement
LINE 1: SET $1 := $2
^

Looks to me like it's the SET it doesn't like.
--
Richard Huxton
Archonet Ltd