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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
