To: tgl@sss.pgh.pa.us
Cc: sszabo@megazone23.bigpanda.com, pgsql-sql@postgresql.org
Date: Tue, 17 Jun 2003 11:15:55 -0500
Subject: Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL--THANKS!
Message-ID: <20030617.111556.-16301703.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>


On Tue, 17 Jun 2003 00:42:54 -0400 Tom Lane <tgl@sss.pgh.pa.us> writes:
> 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
> 
> 

Stephan, Tom,

Thank you both for your insights.  You were right, Stephan, declaring
IndRec as a RECORD type
worked !  That and a couple other no doubt "quick and dirty" fixes and
the function ran exactly how it was supposed to.

Again, thank you both, and everyone on the pgsql-SQL list for all the
help.

Tony

Tony G. Harris
928 Sibley Street
Hammond, IN 46320-1649
Phone: (219) 931-3546
FAX: (646) 405-8569
email: tgharris_1@juno.com
----------------------------
I returned, and saw under the sun, that the race is not to the swift, nor
the battle to the strong, neither yet bread to the wise, nor yet riches
to men of understanding, nor yet favour to men of skill; but time and
chance happeneth to them all.
-- Ecclesiastes 9:11