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
Message-ID: <Sea2-F58JlWZO8x96Pn00023c08@hotmail.com> (raw)
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
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: thedumbkid@hotmail.com
Subject: Re: generic return for functions
In-Reply-To: <Sea2-F58JlWZO8x96Pn00023c08@hotmail.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