Can anyone tell me why the same function tried on POSTGRESQL 7.4 works but does nor work on POSTGRESQL 8.0. Is there any difference in the way POSTGRES handles functions in version 8.0
CREATE OR REPLACE FUNCTION sp_delete_course()
RETURNS text AS
'
Declare
COURSEID VARCHAR(20);
NEWCOURSEID VARCHAR(20);
SET NEWCOURSEID = ''ARC'' + COURSEID;
INSERT INTO ARCHIVE_ETL..COURSEMASTER SELECT * FROM COURSEMASTER WHERE COURSEID = COURSEID;
INSERT INTO ARCHIVE_ETL..TOPICS SELECT * FROM TOPICS WHERE COURSEID = COURSEID;
INSERT INTO ARCHIVE_ETL..COURSESENROLLED SELECT * FROM COURSESENROLLED WHERE COURSEID = COURSEID;
INSERT INTO ARCHIVE_ETL..SKILLS SELECT * FROM SKILLS WHERE COURSEID = COURSEID;
INSERT INTO ARCHIVE_ETL..REGISTER SELECT R.ID,R.STUDENTID,R.FNAME,R.MNAME,R.LNAME,R.ADDRESS,R.CITY,R.STATE,R.PINCODE,R.TEL,R.EMAIL,R.DOB,R.SEX,R.RELIGION,R.NATIONALITY,R.LANGUAGEK,R.PROFESSION,R.QUALIFICATION,R.EXPERIENCE,R.COMPANYNAME,R.SPECIALITY,R.PASSWORD,R.ENROLLDATE,R.CARDID FROM REGISTER R INNER JOIN COURSESENROLLED C ON R.STUDENTID=C.STUDENTID AND R.FNAME=C.STUDENTNAME WHERE C.COURSEID=COURSEID
INSERT INTO ARCHIVE_ETL..TRAINERMASTER SELECT T.INSTRUCTORID,T.TITE,T.FIRSTNAME,T.LASTNAME,T.DESIGNATION,T.QUALIFICATION,T.ADDRESS,T.TEL,T.EMAILID,T.PASSWORD FROM TRAINERMASTER T INNER JOIN SKILLS S ON T.INSTRUCTORID=S.INSTRUCTORID WHERE S.COURSEID = COURSEID
UPDATE ARCHIVE_ETL..COURSEMASTER SET COURSEID = NEWCOURSEID WHERE COURSEID = COURSEID
UPDATE ARCHIVE_ETL..TOPICS SET COURSEID = NEWCOURSEID WHERE COURSEID = COURSEID
UPDATE ARCHIVE_ETL..COURSESENROLLED SET COURSEID = NEWCOURSEID WHERE COURSEID = COURSEID
UPDATE ARCHIVE_ETL..SKILLS SET COURSEID = NEWCOURSEID WHERE COURSEID = COURSEID
DELETE COURSEMASTER WHERE COURSEID = COURSEID
DELETE TOPICS WHERE COURSEID = COURSEID
DELETE COURSESENROLLED WHERE COURSEID = COURSEID
DELETE SKILLS WHERE COURSEID = COURSEID
'
LANGUAGE 'plpgsql' VOLATILE;
I get the following error...
ERROR: type "newcourseid" does not exist
CONTEXT: compile of PL/pgSQL function "sp_delete_course" near line 4