Is this an ODBC question? This might be a better question for the pgsql-general mailing list.
That said, perhaps you should use pgAdmin to cut the definition out of 7.4 database, and apply it to the
8.0 database.
Minal A. Aryamane wrote:
> Can anyone tell me why the same function tried on POSTGRESQL 7.4 works but does nor work on POSTGRESQL 8.0. Is there
anydifference 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
FROMREGISTER 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.PASSWORDFROM
TRAINERMASTERT 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
>
>
>
>