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