Обсуждение: problems returning a resultset from a function

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';


and the java

CallableStatement cs = null;
ResultSet rs = null;
cs = con.prepareCall("{ ? = call test()}");
cs.registerOutParameter(1, java.sql.Types.OTHER);
rs = cs.executeQuery();
rs.next();
System.out.println("name: " +rs.getString(1));

but throws te following error:

cursor "<unnamed portal 1>" does not exist

I try this too:

Statement s = null;
rs = s.executeQuery("select test()");
rs.next();
System.out.println("name: " +rs.getString(1));

and don't throws an error, but show me the following
result:

name: <unnamed portal 1>

anybody can help me with this?

thnx;





__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

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