Skip site navigation (1) Skip section navigation (2)

Re: stored procedure returning result set.

From: "Gohil, Hemant" <HGohil(at)dylt(dot)com>
To: "Kris Jurka" <books(at)ejurka(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: stored procedure returning result set.
Date: 2008-09-23 22:05:09
Message-ID: A094C51321D00949B7A791E1AA7CFCC2116EA300@lbcamx01.corp.dylt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi Kris, 

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
Client ?

Thanks again for the links

Hemant

-----Original Message-----
From: Kris Jurka [mailto:books(at)ejurka(dot)com] 
Sent: Tuesday, September 23, 2008 1:16 PM
To: Gohil, Hemant
Cc: pgsql-jdbc(at)postgresql(dot)org
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
>
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 
functions.

> 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()".

http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resul
tset-setof

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.

http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resul
tset-refcursor

Kris Jurka


In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-09-23 22:21:12
Subject: Re: stored procedure returning result set.
Previous:From: Michael EnkeDate: 2008-09-23 20:32:13
Subject: Re: PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group