Trouble porting a Oracle PL/SQL procedure to PL/pgSQL

From: "Tony G(dot) Harris" <tgharris_1(at)juno(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
Date: 2003-06-17 01:57:42
Message-ID: 20030616.205744.-16302819.1.tgharris_1@juno.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The
text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references
Oracle in its examples, though I can usually get PostgreSQL to work
almost as well.

Well, I'm almost to the end of the book and I'm trying to port some of
the book's PL/SQL examples to PL/pgSQL, with mixed success. In this
case, I translated

BEGIN
DECLARE
UnknownPayType EXCEPTION;
CURSOR pay_cursor IS
SELECT name, pay_type, pay_rate, eff_date, sysdate,
rowid
FROM pay_table;
IndRec pay_cursor%ROWTYPE;
. . .
END;
/

to this:

CREATE OR REPLACE FUNCTION update_pay() RETURNS VOID AS '
DECLARE
pay_cursor CURSOR IS
SELECT name, pay_type, pay_rate, eff_date, current_date, oid
FROM pay_table;
IndRec pay_cursor%ROWTYPE;
cOldDate DATE;
. . .
END;
'
LANGUAGE 'plpgsql';

The problem is, when I call the function, I get:

sql-practice=# select update_pay();
ERROR: pay_cursor: no such class
WARNING: plpgsql: ERROR during compile of update_pay near line 2
WARNING: plpgsql: ERROR during compile of update_pay near line 2
ERROR: pay_cursor: no such class

I tried several ways of defining pay_cursor, with the same result.
What am I to do?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2003-06-17 02:17:57 Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
Previous Message Richard Hall 2003-06-17 01:01:25 Re: Our FLOAT(p) precision does not conform to spec