Thread: Re: Beginner problems with functions

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 have to decide:
 1) Can I use PostgreSQL for such querys?    a) If yes, could someone give any pointer to docs/examles/something else
b) If no, please tel me so. That would let switch to b) or c) 2) Backport the MS SQL server functions into plain SQL
textinside    my servlets just to get them working.  Not very clever, but should    work, hopefully. 3) Use another
databaseserver, could be Interbase but I would prefer    PostgreSQL.
 

Could somebody please help me to decide which strongle depends from the
SQL procedure problem.

Kind regards
         Andreas.

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.

On Mon, 21 Aug 2000, Stephan Szabo wrote:

> 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.
OK, this might probably work for the short time.  Could someone give
me any hope for the future that there will be other solutions in higher
versions of PostgreSQL which support the missing feature?

Kind regards
       Andreas.