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?