Skip site navigation (1) Skip section navigation (2)

Re: Re: Beginner problems with functions

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Andreas Tille <tillea(at)rki(dot)de>
Cc: PostgreSQL SQL <pgsql-sql(at)hub(dot)org>
Subject: Re: Re: Beginner problems with functions
Date: 2000-08-21 16:47:12
Message-ID: Pine.BSF.4.10.10008210932010.16744-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.



In response to

Responses

pgsql-sql by date

Next:From: Mark VolpeDate: 2000-08-21 17:26:39
Subject: NULL function arguments?
Previous:From: Webb SpragueDate: 2000-08-21 16:20:36
Subject: Re: Best way to create DML/DDL log?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group