To: pgsql-sql@postgresql.org
Date: Mon, 16 Jun 2003 20:57:42 -0500
Subject: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
Message-ID: <20030616.205744.-16302819.1.tgharris_1@juno.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
From: "Tony G. Harris" <tgharris_1@juno.com>

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?