Thread: Difference

"Minal A. Aryamane" <minalac@yes2etl.com> writes:
> 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

There are some ... but the code you show would not have worked in *any*
release of PG.  It's certainly missing a BEGIN, and "SET" is not the
syntax for assigning to a local plpgsql variable, and quite a few
semicolons are obviously missing, and you didn't bother to return any
result value.

Could we have an unedited transcription of both the function source code
and the error message, please?

            regards, tom lane

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