Re: Stored procedures..PLEASE HELP

Hello,

          I am alagu madhu working as a Developer (postgresql).pls,send
your table design.






"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
> '
> Declare
> COURSEIDS VARCHAR(1000) ;
> TOPICIDS VARCHAR(1000);
> COURSENAME VARCHAR(150);
> USERNAME VARCHAR(20);
>
> COURSEIDS1 VARCHAR(1000);
> TOPICIDS1 VARCHAR(1000);
> COURSEID1 VARCHAR(20);
> TOPICID1 VARCHAR(20);
> NEWCOURSEID VARCHAR(20);
> NEWTOPICID VARCHAR(20);
> COLUMN1 VARCHAR(50);
> COLUMN2 VARCHAR(50);
> COLUMN3 VARCHAR(50);
> COLUMN4 VARCHAR(50);
> QCODE1 VARCHAR(10);
> QDATE1 VARCHAR(10);
> QSNO INTEGER;
> WEIGHTAGE INTEGER;
> QSTYPE VARCHAR(50);
> QUESTION VARCHAR(1000);
> MARKS VARCHAR(50);
> QFLAG BOOL;
> PFLAG BIT;
> PAPER_TYPE VARCHAR(20);
> CINDEX INTEGER;
> TINDEX INTEGER;
> INDEX INTEGER;
> QINDEX INTEGER;
> QUESTIONID INTEGER;
> NEWQUESTIONID INTEGER;
> LESSON_FLOW_TITLE VARCHAR(50);
> TREE_NODE_TYPE VARCHAR(50);
> VIDEO_SIZE INTEGER;
> ROOT_NODE_ID INTEGER;
> SLIDE_ORDER INTEGER;
> row1 record;
> row2 record;
> row3 record;
> COURSENAME1 varchar(150);
> USERNAME1 varchar(50);
>
> BEGIN
> SET COURSEIDS1:=COURSEIDS;
> SET TOPICIDS1:=TOPICIDS;
> SET COURSENAME1:=COURSENAME;
> SET USERNAME1:=USERNAME;
>
> --PRINT \'INSIDE THE STORED PROCEDURE\'
>
> COLUMN1 := DATE_PART(\'Day\',current_date);
> COLUMN2 := DATE_PART(\'Month\',current_date);
> COLUMN3 := DATE_PART(\'Year\',current_date);
>
> IF LENGTH(COLUMN1) = 1 THEN
>  COLUMN1 := \'0\' || COLUMN1;
> END IF;
> IF LENGTH(COLUMN2) = 1 THEN
>  COLUMN2 := \'0\' || COLUMN2;
> END IF;
>
> QDATE1 := COLUMN1 || COLUMN2 || COLUMN3;
>
> SELECT trim(to_char(max(to_number(QCODE,\'999999\'))+1,\'000009\')) INTO QCODE1 FROM QUESTION WHERE QDATE = QDATE1;
>
>  WHILE  (LENGTH(QCODE1) < 6) LOOP
>   QCODE1 := \'0\' || QCODE1;
>  END LOOP;
>
> SELECT MAX(SUBSTR(COURSEID,7,LENGTH(COURSEID))) INTO  NEWCOURSEID FROM COURSEMASTER;
>
>  WHILE  (LENGTH(NEWCOURSEID) < 4) LOOP
>   NEWCOURSEID := \'0\' || NEWCOURSEID;
>  END LOOP;
>   NEWCOURSEID := \'ETLCRC\' || NEWCOURSEID;
>   INSERT INTO COURSEMASTER(COURSEID, COURSENAME) VALUES ( NEWCOURSEID,COURSENAME1);
>   CINDEX := POSITION(\':\' IN COURSEIDS1);
>   TINDEX := POSITION(\':\' IN TOPICIDS1);
>   INDEX := 1;
>  WHILE (CINDEX >  0) LOOP
>   COURSEID1 := SUBSTR(COURSEIDS1,1,(CINDEX-1));
>   TOPICID := SUBSTR(TOPICIDS1,1,(TINDEX-1));
>
>  -- TRANSACTION STARTS HERE
>   NEWTOPICID := INDEX;
>    WHILE  (LENGTH(NEWTOPICID) < 4) LOOP
>     NEWTOPICID := \'0\' || NEWTOPICID;
>    END LOOP;
>    NEWTOPICID := \'CRCSUB\' || NEWTOPICID;
>    INSERT INTO MYTABLE6 VALUES(COURSEID1, TOPICID, NEWCOURSEID, NEWTOPICID);
>    INSERT INTO TOPICS SELECT NEWCOURSEID, NEWTOPICID, TOPICNAME  FROM TOPICS WHERE COURSEID=COURSEID AND
TOPICID=TOPICID;
>    SELECT NULLIF(0,COUNT(*)) INTO COLUMN3 FROM LESSON_FLOW WHERE COURSEID=COURSEID AND TOPICID=TOPICID;
>    COLUMN1 := COURSEID1 || \'_\' || TOPICID || \'_\';
>    COLUMN2 := NEWCOURSEID || \'_\' || NEWTOPICID || \'_\';
>    IF (COLUMN3 >0) THEN
>     SELECT MAX(ATTACHMENT_ID) + 1 INTO COLUMN3 FROM COURSE_LESSON;
>     INSERT INTO COURSE_LESSON(COURSEID1, TOPICID, RECORD_STATUS, ATTACHMENT_ID) VALUES (NEWCOURSEID, NEWTOPICID,0,
CONVERT(INT,COLUMN3)); 
>     INSERT INTO ATTACHMENT SELECT COLUMN3, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)), SEQUENCE FROM ATTACHMENT WHERE
SUBSTR(URL,0,23)= COLUMN1; 
>     SELECT MAX(LF_ID) + 1 INTO COLUMN4 FROM LESSON_FLOW;
>
>     FOR row1 IN SELECT  LESSON_FLOW_TITLE, TREE_NODE_TYPE, VIDEO_SIZE, ROOT_NODE_ID, SLIDE_ORDER FROM LESSON_FLOW
WHERECOURSEID=COURSEID AND TOPICID = TOPICID 
>     LOOP
>      LESSON_FLOW_TITLE:=row1.LESSON_FLOW_TITLE;
>      TREE_NODE_TYPE:=row1.TREE_NODE_TYPE;
>      VIDEO_SIZE:=row1.VIDEO_SIZE;
>      ROOT_NODE_ID:=row1.ROOT_NODE_ID;
>      SLIDE_ORDER:=row1.SLIDE_ORDER;
>
>      INSERT INTO LESSON_FLOW  VALUES (COLUMN4, NEWCOURSEID, NEWTOPICID, LESSON_FLOW_TITLE, TREE_NODE_TYPE,
VIDEO_SIZE,COLUMN3, ROOT_NODE_ID, SLIDE_ORDER); 
>      COLUMN4 := COLUMN4 + 1;
>
>     END LOOP;
>     INSERT INTO SELECTEDSESSION SELECT NEWCOURSEID, NEWTOPICID, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)) FROM
SELECTEDSESSIONWHERE COURSEID= COURSEID1 AND TOPICID= TOPICID; 
>    END IF;
>    QINDEX := 1;
>
>   FOR row2 IN SELECT QUESTION_ID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, FLAG,  PAPER_TYPE FROM QUESTION WHERE
COURSEID=COURSEIDAND TOPICID = TOPICID 
>   LOOP
>    QUESTIONID:=row2.QUESTION_ID;
>    QSNO:=row2.QSNO;
>    WEIGHTAGE:=row2.WEIGHTAGE;
>    QSTYPE:=row2.QSTYPE;
>    QUESTION:=row2.QUESTION;
>    MARKS:=row2.MARKS;
>    QFLAG:=row2.FLAG;
>    PAPER_TYPE:=row2.PAPER_TYPE;
>
>    INSERT INTO QUESTION (COURSEID1, TOPICID, QSNO, WEIGHTAGE, QSTYPE, QUESTION, MARKS, QDATE, QCODE,FLAG, USERNAME,
PAPER_TYPE)VALUES (NEWCOURSEID, NEWTOPICID, QINDEX, WEIGHTAGE, QSTYPE, QUESTION, MARKS, QDATE1, QCODE,QFLAG, USERNAME1,
PAPER_TYPE);
>    NEWQUESTIONID := IDENTITY;
>    INSERT INTO OPTIONS SELECT NEWQUESTIONID, OPTIONS, CORRECTOPTION FROM OPTIONS WHERE QUESTION_ID=QUESTIONID;
>    QCODE1 :=QCODE1 + 1;
>    WHILE (LENGTH(QCODE1) < 6) LOOP
>     QCODE1 := \'0\' || QCODE1;
>    END LOOP;
>    QINDEX := QINDEX + 1;
>   END LOOP;
>   QINDEX := 1;
>
>   FOR row3 IN SELECT QUESTION_ID, QSNO,  QSTYPE, QUESTION, FLAG FROM POLL_QUESTION WHERE COURSEID=COURSEID AND
TOPICID= TOPICID 
>   LOOP
>    QUESTIONID:=row3.QUESTIONID;
>    QSNO:=row3.QSNO;
>    QSTYPE:=row3.QSTYPE;
>    QUESTION:=row3.QUESTION;
>    PFLAG:=row3.FLAG;
>     INSERT INTO POLL_QUESTION (COURSEID1, TOPICID, QSNO, QSTYPE, QUESTION, QDATE, QCODE,FLAG, USERNAME) VALUES
(NEWCOURSEID,NEWTOPICID, QINDEX, QSTYPE, QUESTION, QDATE1, QCODE1,PFLAG, USERNAME1); 
>     NEWQUESTIONID := IDENTITY;
>     INSERT INTO POLL_OPTIONS SELECT NEWQUESTIONID, OPTIONS, SUB_OPTIONS, TYPE,DISPLAY FROM POLL_OPTIONS WHERE
QUESTION_ID=QUESTIONID;
>     QCODE1 := QCODE1 + 1;
>     WHILE  (LENGTH(QCODE1) < 6) loop
>      QCODE1 := \'0\' || QCODE1;
>     END LOOP;
>     QINDEX := QINDEX + 1;
>   END LOOP;
>
> COURSEIDS1 := SUBSTR(COURSEIDS1,(CINDEX+1),LENGTH(COURSEIDS1));
> TOPICIDS1 := SUBSTR(TOPICIDS1,(TINDEX+1),LENGTH(TOPICIDS1));
> CINDEX := POSITION(\':\' IN COURSEIDS1);
> TINDEX := POSITION(\':\' IN TOPICIDS1);
> INDEX := INDEX + 1;
>
> END LOOP;
>
> RETURN \'1\';
> END;
>
> '
>   LANGUAGE 'plpgsql' VOLATILE;
>
> 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.
>
> Can someone please look into the procedure and tell me where I have gone wrong.