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