Tuesday, September 23, 2008

Re: [JDBC] stored procedure returning result set.

On Tue, 23 Sep 2008, Gohil, Hemant wrote:

> I am trying to convert a working SQL query to stored function and get
> the resultset back.
> select * FROM sp_allCodes(1542) AS
> myresult(firstColumn,codeid,category,categoryid,CdLnk,codevalue,allcodes
> desc,codelink,maskfmt,sortseqnumber,adddate,changedate,addopid,changeopi
> d,allcodeslongdesc)
> ERROR: a column definition list is required for functions returning
> "record"

You need type information as well in the output list for record returning

> Basically I am using Sybase ASE presently and I am exploring the option
> to migrate to PostgreSQL, for Sybase I would just write
> Execute sp_allCodes 1542
> and it will return the result set with all the columns and rows. Is
> there a way to achieve similar functionality ?

To use "setof record" you must explicitly name the output colums in the
select. Other options are to create a new type to represent the output of
the function ("returning setof mytype") or to use output parameters. In
that case you can just say "select * from myfunc()".


The final option is to return a refcursor which you can then turn into a
ResultSet. This is the most flexible as it doesn't require naming the
columns during function creation or function execution.


Kris Jurka

Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:

No comments: