[JDBC] 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