Stored procedures..PLEASE HELP

Поиск

Список

Период

Сортировка

От	Minal A. Aryamane
Тема	Stored procedures..PLEASE HELP
Дата	17 января 2006 г. 07:32:55
Msg-id	002101c61b40$24b2c3b0$1502010a@aarnbee.com обсуждение исходный текст
Ответы	Re: Stored procedures..PLEASE HELP (Richard Huxton <dev@archonet.com>) Re: Stored procedures..PLEASE HELP ("Alagu Madhu" <almadhu@gmail.com>)
Список	pgsql-general

Дерево обсуждения

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);

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 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;

     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;

  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.

В списке pgsql-general по дате отправления:

Предыдущее

От: "R, Rajesh (STSD)"
Дата: 17 января 2006 г., 06:07:43
Сообщение: Re: [PATCH] Better way to check for getaddrinfo function.

Следующее

От: Richard Huxton
Дата: 17 января 2006 г., 08:29:01
Сообщение: Re: Stored procedures..PLEASE HELP

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Stored procedures..PLEASE HELP

Предыдущее

Следующее