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
