Date: Sun, 1 Jun 2003 00:09:54 -0500
Subject: Re: generic return for functions
Content-Type: text/plain; charset=US-ASCII; format=flowed
Mime-Version: 1.0 (Apple Message framework v552)
From: Avi Schwartz <avi@CFFtechnologies.com>
To: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
In-Reply-To: <Sea2-F58JlWZO8x96Pn00023c08@hotmail.com>
Message-Id: <47F83F6F-93EF-11D7-ACB0-000393AE5044@CFFtechnologies.com>

I am afraid you are in exactly the same boat I am in.  Coldfusion does 
not recognize the refcursor and as far as I know, currently the only 
way to return a result set that CF can read is to return a setof which 
requires you to return a user defined data type or a record.  Another 
thing that causes me some minor grief is the fact that currently you 
cannot have default values to function parameters, a feature we use a 
lot.

Avi

On Saturday, May 31, 2003, at 23:02 America/Chicago, Danny Su wrote:

> 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 :)
-- 
Avi Schwartz
avi@CFFtechnologies.com