From: Avi Schwartz <avi@CFFtechnologies.com>
To: pgsql-sql@postgresql.org
Subject: Re: generic return for functions
Date: Sun, 1 Jun 2003 00:09:54 -0500
Message-ID: <47F83F6F-93EF-11D7-ACB0-000393AE5044@CFFtechnologies.com> (raw)
In-Reply-To: <Sea2-F58JlWZO8x96Pn00023c08@hotmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: avi@CFFtechnologies.com
Subject: Re: generic return for functions
In-Reply-To: <47F83F6F-93EF-11D7-ACB0-000393AE5044@CFFtechnologies.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox