Re: generic return for functions - Mailing list pgsql-sql

From Avi Schwartz
Subject Re: generic return for functions
Date
Msg-id 47F83F6F-93EF-11D7-ACB0-000393AE5044@CFFtechnologies.com
Whole thread Raw
In response to generic return for functions  ("Danny Su" <thedumbkid@hotmail.com>)
Responses Re: generic return for functions
List pgsql-sql
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



pgsql-sql by date:

Previous
From: "Danny Su"
Date:
Subject: generic return for functions
Next
From: Joe Conway
Date:
Subject: Re: generic return for functions