Re: problems returning a resultset from a function

Leo Martin Orfei said:

> hi.
>
> I have a problem with postgres functions.
> I need return a resultset from a postgres function and
> browse the resultset in a java app.
> I try with this simple function:
>
> create function test() returns catalog.refcursor as'
> declare aux refcursor;
> BEGIN
> OPEN aux FOR SELECT name, address FROM table;
> RETURN aux;
> END;
> 'LANGUAGE 'plpgsql';
>

My apologies if this has been responded to already as I am not currently
reading pgsql-jdbc and the archive three days behind. AFAIK there isn't
support for embedded work in jdbc, so it doesn't seem returning a cursor
directly would help (well maybe there is a way...I don't know).

In order to accomplish what I think you want to accomplish (which is to
establish a jdbc resultset object from a stored function) I've done the
following in the past:

Create a pl/pgsql function that returns a rowtype, DECLARED as follows:

-- the "table" in the following refers to an existing table definition
row table%rowtype;

In the pl/pgsql script you have something like:

-- return each row in the result set
for row in SELECT name, address FROM table loop
return next row;
end loop;
return;

Then in java call this using just a regular statement object:

// test() is the name of the stored function.
rs = statement.executeQuery("select * from test()");
while (rs.next()) {
...code to browse/process the rows...
}

Note the above examples might have a typo or two...but that's the general
idea. I have not used the stored procedure statement object as you did.
Perhaps that would be more portable.

Best,

Jim

--
Jim Wilson - IT Manager
Kelco Industries
PO Box 160
58 Main Street
Milbridge, ME 04658
207-546-7989 - FAX 207-546-2791
http://www.kelcomaine.com