From: "Danny Su" <thedumbkid@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: generic return for functions
Date: Sun, 01 Jun 2003 00:02:22 -0400
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <Sea2-F58JlWZO8x96Pn00023c08@hotmail.com>

Hi, this is my first time using mailing list.  Someone over at tek-tips 
suggested that I try here to see if someone can help me.

I am currently converting everything from SQL Server to PostgreSQL.  This is 
for an application that is going to support Oracle, SQL Server and 
PostgreSQL at the same time.  I have done a lot of the conversion already 
but I am stuck on functions that returns parts of views or tables.

In SQL Server, you can create User Defined functions that returns type 
"TABLE"... so then you can simply return the result of a select statement... 
(e.g. return select * from mytable)
The problem is that I don't know if there is a way to do this in PostgreSQL.
My functions and stored procedures in SQL Server involves select statement 
that gets columns from few views and tables.
I know I can create my own data type with all the columns that are going to 
be returned, or get my function to return a type "record"... however, I 
don't like both of these methods since some of my functions involves 
returning a select statement using inner join and all that stuff... some 
have like 30 columns

There seems to be another way to use refcursor but my application is in 
ColdFusion... refcursor doesn't seem to work with it.

I know "returns setof record" and "returns setof my_own_datatype" work, but 
I would like to know if there is a better way? Something that's like 
"returns setof record" but without having to define all the columns when I 
call the function? {i.e. without the need to do: select * from myfunction() 
as (column1 type1, column2...);}

If there is such method? It will allow me to maintain the application much 
easier and makes the conversion task much easier :)

Thanks.
/Danny

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.  
http://join.msn.com/?page=features/junkmail