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,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