Thread: Stored procedures..PLEASE HELP
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
'
Declare
COURSEIDS VARCHAR(1000) ;
TOPICIDS VARCHAR(1000);
COURSENAME VARCHAR(150);
USERNAME VARCHAR(20);
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);
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;
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);
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;
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;
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));
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;
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 WHERE COURSEID=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;
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 SELECTEDSESSION WHERE 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=COURSEID AND 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;
COLUMN4 := COLUMN4 + 1;
END LOOP;
INSERT INTO SELECTEDSESSION SELECT NEWCOURSEID, NEWTOPICID, COLUMN2 + SUBSTR(URL,23,LENGTH(URL)) FROM SELECTEDSESSION WHERE 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=COURSEID AND 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;
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;
TOPICIDS1 := SUBSTR(TOPICIDS1,(TINDEX+1),LENGTH(TOPICIDS1));
CINDEX := POSITION(\':\' IN COURSEIDS1);
TINDEX := POSITION(\':\' IN TOPICIDS1);
INDEX := INDEX + 1;
END LOOP;
RETURN \'1\';
END;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
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.
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.
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
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.