Обсуждение: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
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 SELECTname, 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?
On Mon, 16 Jun 2003, Tony G. Harris wrote: > 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? I think it's complaining because you can't use pay_cursor%ROWTYPE. Maybe declaring IndRec as being of type record may work (don't know for certain).
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 16 Jun 2003, Tony G. Harris wrote:
>> 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
>> ERROR: pay_cursor: no such class
> I think it's complaining because you can't use pay_cursor%ROWTYPE.
Yeah, you're right. I didn't believe that theory at first because
the line number reference didn't point at the line with %ROWTYPE ...
but upon digging into it I find that plpgsql's code for determining
the line number to report is flat wrong for this case. (I've committed
a quick fix into CVS tip, but I wonder whether the whole mechanism
shouldn't be rethought. Calling plpgsql_scanner_lineno() all over the
place doesn't seem real clean.)
> Maybe declaring IndRec as being of type record may work (don't know
> for certain).
That's what I'd try. We don't consider that declaring a cursor creates
a named rowtype ... I'm surprised that Oracle seems to think it does.
regards, tom lane