Re: Re: Beginner problems with functions

On Mon, 21 Aug 2000, Andreas Tille wrote:

> On Thu, 17 Aug 2000, Stephan Szabo wrote:
>
> > What you may need to do is declare a variable of type record
> > and do SELECT INTO <variable> * From ... rather than just
> > the SELECT.
> Thanks, that worked.
>
> > Yeah, setof <record type> seems fairly wierd. SETOF basetype if
> > you do a SELECT <col> FROM table seems to work though.
> > I sort of expected that the ones in the regression test would
> > either do something understandable or at least error if they
> > are testing for brokenness.
> Is there any kind of documentation how to cope with that problem?
>
> I try to explain my problem once more:
>
> My servlets contain code like:
>
> rs = stmt.executeQuery("stored_procedure arg1, arg2");
> while ( rs.next() )
> do_something(rs.getString("col1"), rs.getString("col2"),
> rs.getString("col3"), rs.getString("col4") );
>

I haven't thought of an elegant way to do it, although you could
fake some of it with a table of the appropriate structure with a sequence.
It's really ugly, but the pl/sql(tcl/perl/etc...) function gets the
next value of the sequence and inserts the results into a table with
the sequence number and returns the number to you.

So, it'd be something like
select stored_procedure(arg1, arg2);
(get the value into variable)
select * from table_sp_<name> where intval=<variable>
while (...)
do_something(...)
delete from table_sp_<name> where intval=<variable>

That might be safe in so far as the sequence number should stop
concurrent transactions from clobbering each other, but it requires
that you do the deletes manually and that table will need to be
vacuumed fairly often probably.