Thank you very much for links. I was able to create the function fine.
If it is not for asking too much - how would I display actual results
from the cursor using SQL clients like Aqua Data Studio or SQuirreL SQL
Thanks again for the links
From: Kris Jurka [mailto:books(at)ejurka(dot)com]
Sent: Tuesday, September 23, 2008 1:16 PM
To: Gohil, Hemant
Subject: 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
> ERROR: a column definition list is required for functions returning
You need type information as well in the output list for record
> Basically I am using Sybase ASE presently and I am exploring the
> 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
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.
In response to
pgsql-jdbc by date
|Next:||From: Kris Jurka||Date: 2008-09-23 22:21:12|
|Subject: Re: stored procedure returning result set.|
|Previous:||From: Michael Enke||Date: 2008-09-23 20:32:13|
|Subject: Re: PreparedStatement.toString() creates valid SQL; was: Q:
use setObject also for int or string|