Difference

This is a multi-part message in MIME format.

------=_NextPart_000_001F_01C61512.A7A32780
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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);=20
NEWCOURSEID VARCHAR(20);
SET NEWCOURSEID =3D ''ARC'' + COURSEID;

INSERT INTO ARCHIVE_ETL..COURSEMASTER SELECT * FROM COURSEMASTER WHERE =
COURSEID =3D COURSEID;
INSERT INTO ARCHIVE_ETL..TOPICS SELECT * FROM TOPICS WHERE COURSEID =3D =
COURSEID;
INSERT INTO ARCHIVE_ETL..COURSESENROLLED SELECT * FROM COURSESENROLLED =
WHERE COURSEID =3D COURSEID;
INSERT INTO ARCHIVE_ETL..SKILLS SELECT * FROM SKILLS WHERE COURSEID =3D =
COURSEID;
INSERT INTO ARCHIVE_ETL..REGISTER SELECT =
R.ID,R.STUDENTID,R.FNAME,R.MNAME,R.LNAME,R.ADDRESS,R.CITY,R. STATE,R.PINCO=
DE,R.TEL,R.EMAIL,R.DOB,R.SEX,R.RELIGION,R.NATIONALITY,R.LANG UAGEK,R.PROFE=
SSION,R.QUALIFICATION,R.EXPERIENCE,R.COMPANYNAME,R.SPECIALIT Y,R.PASSWORD,=
R.ENROLLDATE,R.CARDID FROM REGISTER R INNER JOIN COURSESENROLLED C ON =
R.STUDENTID=3DC.STUDENTID AND R.FNAME=3DC.STUDENTNAME WHERE =
C.COURSEID=3DCOURSEID
INSERT INTO ARCHIVE_ETL..TRAINERMASTER SELECT =
T.INSTRUCTORID,T.TITE,T.FIRSTNAME,T.LASTNAME,T.DESIGNATION,T .QUALIFICATIO=
N,T.ADDRESS,T.TEL,T.EMAILID,T.PASSWORD FROM TRAINERMASTER T INNER JOIN =
SKILLS S ON T.INSTRUCTORID=3DS.INSTRUCTORID WHERE S.COURSEID =3D =
COURSEID
UPDATE ARCHIVE_ETL..COURSEMASTER SET COURSEID =3D NEWCOURSEID WHERE =
COURSEID =3D COURSEID
UPDATE ARCHIVE_ETL..TOPICS SET COURSEID =3D NEWCOURSEID WHERE COURSEID =
=3D COURSEID
UPDATE ARCHIVE_ETL..COURSESENROLLED SET COURSEID =3D NEWCOURSEID WHERE =
COURSEID =3D COURSEID
UPDATE ARCHIVE_ETL..SKILLS SET COURSEID =3D NEWCOURSEID WHERE COURSEID =
=3D COURSEID
DELETE COURSEMASTER WHERE COURSEID =3D COURSEID
DELETE TOPICS WHERE COURSEID =3D COURSEID
DELETE COURSESENROLLED WHERE COURSEID =3D COURSEID
DELETE SKILLS WHERE COURSEID =3D 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




------=_NextPart_000_001F_01C61512.A7A32780
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Difference

"Minal A. Aryamane" 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Difference

This is a multi-part message in MIME format.
--------------020108070203090506040200
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 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.NATIO NALITY,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
>
>
>
>

--------------020108070203090506040200
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------020108070203090506040200
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--------------020108070203090506040200--