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

stored procedure returning result set.

From: "Gohil, Hemant" <HGohil(at)dylt(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: stored procedure returning result set.
Date: 2008-09-23 19:09:54
Message-ID: A094C51321D00949B7A791E1AA7CFCC2116EA2EF@lbcamx01.corp.dylt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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,allcodes
desc,codelink,maskfmt,sortseqnumber,adddate,changedate,addopid,changeopi
d,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

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2008-09-23 20:15:38
Subject: Re: stored procedure returning result set.
Previous:From: Albe LaurenzDate: 2008-09-23 06:17:24
Subject: Re: COPY support in JDBC driver?

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