Thread: stored procedure returning result set.
Hi,
I am trying to convert a working SQL query to stored function and get the resultset back.
Here is my actual function
==============================================================
CREATE FUNCTION sp_allCodes(integer) RETURNS SETOF record AS $$
DECLARE
acodes RECORD;
BEGIN
FOR acodes in
SELECT
case
when $1 = codeId then '******'
else ''
end AS "firstColumn" ,
allCodes.codeId,
category,
allCodes.categoryId,
SUBSTRING(dataValue, 1, 8) AS "CdLnk",
allCodes.codeValue,
allCodes.allCodesDesc,
allCodes.codeLink,
allCodes.maskfmt,
allCodes.sortSeqNumber,
allCodes.addDate,
allCodes.changeDate,
allCodes.addOpId,
allCodes.changeOpId,
allCodes.allCodesLongDesc
FROM allCodes INNER JOIN category ON allCodes.categoryId = category.categoryId LEFT OUTER JOIN codeLink ON codeId = codeLinkId
WHERE allCodes.categoryId in
(
SELECT categoryId
FROM allCodes
WHERE codeId = $1
)
ORDER BY codeId LOOP
return next acodes;
END LOOP;
END;
$$ LANGUAGE plpgsql;
GO
================================================================
Here is how I am calling it,
==========
select * FROM sp_allCodes(1542) AS myresult(firstColumn,codeid,category,categoryid,CdLnk,codevalue,allcodesdesc,codelink,maskfmt,sortseqnumber,adddate,changedate,addopid,changeopid,allcodeslongdesc)
============
And getting following error -
ERROR: a column definition list is required for functions returning "record"
I have also tried calling using
select * FROM sp_allCodes(1542)
Which gave the same error.
============================================================================================
I am using Aqua Data Studio as my SQL client.
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 ?
We have lots of stored procedure and it would be extremely cumbersome to list ALL the columns getting returned from the function/stored procedure. Apart from that we had to modify our Java code accessing the data as well and XML files containing the SQL and that would make it proprietary for PostgreSQL, I would prefer to keep them as portable as possible.
I would appreciate any suggestions, links etc in this regards.
Thanks in advance
Hemant
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-resultset-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-resultset-refcursor
Kris Jurka
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@ejurka.com]
Sent: Tuesday, September 23, 2008 1:16 PM
To: Gohil, Hemant
Cc: pgsql-jdbc@postgresql.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
On Tue, 23 Sep 2008, Gohil, Hemant wrote:
> 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 ?
I'm not familiar with either of those clients, but here's how you would do
it in psql using the function definition from example 6.3 of the
documentation I pointed you to.
jurka=# begin;
BEGIN
jurka=# select refcursorfunc();
refcursorfunc
--------------------
<unnamed portal 1>
(1 row)
jurka=# fetch all from "<unnamed portal 1>";
?column?
----------
1
2
(2 rows)
Kris Jurka