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
storedprocs. 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