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