Thread: generic return for functions
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
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
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.
>
[...snip description of SQL Server 2000 table-valued UDFs...]
>
> 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 :)
>
Sorry -- the answer is no. But I don't think Oracle will support what
you want either.
Joe
> 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. The default value is used when the parameter is NULL or unprovided? fn(integer, integer, integer default 32) select fn(integer, integer); <- Third argument would be '32'? When PostgreSQL gets named parameters the above probably makes sense to add. A TODO item? -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Exactly. It is used with named parameters when the parameter is not provided. Avi On Sunday, Jun 1, 2003, at 07:21 America/Chicago, Rod Taylor wrote: >> 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. > > The default value is used when the parameter is NULL or unprovided? > > fn(integer, integer, integer default 32) > > select fn(integer, integer); <- Third argument would be '32'? > > > When PostgreSQL gets named parameters the above probably makes sense to > add. > > A TODO item? -- Avi Schwartz avi@CFFtechnologies.com
Rod Taylor <rbt@rbt.ca> writes:
>> thing that causes me some minor grief is the fact that currently you=20
>> cannot have default values to function parameters, a feature we use a=20
>> lot.
> fn(integer, integer, integer default 32)
> select fn(integer, integer); <- Third argument would be '32'?
> When PostgreSQL gets named parameters the above probably makes sense to
> add.
> A TODO item?
That isn't ever going to happen. We have enough difficulty resolving
overloaded functions as it is. If we couldn't even be sure how many
arguments there were supposed to be, it'd become completely intractable.
You can however achieve similar effects at the user level by adding
auxiliary functions: declare fn(int,int) as a one-line SQL function
that calls fn($1, $2, 32).
regards, tom lane
On Sunday, Jun 1, 2003, at 10:46 America/Chicago, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: >>> thing that causes me some minor grief is the fact that currently >>> you=20 >>> cannot have default values to function parameters, a feature we use >>> a=20 >>> lot. > >> fn(integer, integer, integer default 32) >> select fn(integer, integer); <- Third argument would be '32'? >> When PostgreSQL gets named parameters the above probably makes sense >> to >> add. > >> A TODO item? > > That isn't ever going to happen. We have enough difficulty resolving > overloaded functions as it is. If we couldn't even be sure how many > arguments there were supposed to be, it'd become completely > intractable. > > You can however achieve similar effects at the user level by adding > auxiliary functions: declare fn(int,int) as a one-line SQL function > that calls fn($1, $2, 32). > > regards, tom lane I understand why it will not be implemented with overloaded functions. Is there a possibility to allow this only for functions that are not overloaded? The SQL function solution is really not going to help in my case since the function builds a select statement dynamically based on which parameters have a non-null value. The number of parameters is something like 12 or 13 and the control on which parameters are set is determined by a complex combination of program logic and user selections. What I did to solve this problem was to force all variables to be initialized to null and then set the non-null ones before the call to the function. On another note, somewhat related, when we started looking at a replacement to SQL Server 7, I looked at SAPDB, MySQL and now PostgreSQL. MySQL lost immediately since the current version is missing a lot of functionality we were looking for. I was not impressed by SAPDB's performance, their documentation is extremely hard to follow and I found the database very hard to manage. So far PostgreSQL seems to be the best choice for us. I am yet to find a show stopper and the speed is fantastic. I didn't do extensive comparisons yet and I don't have hard numbers, but from what I have seen so far, PostgreSQL 7.3.2 is at least as fast as SQL Server 7 in real life situations (Of course count(*) is still much faster in SQL Server for very large tables (some of our tables are > 5M rows) :-) . What makes it more impressive is the fact that SS runs on a 4 CPU machine with 2 GB of memory while PostgreSQL on a single CPU machine with 384M memory running SuSE 8.2. In the near future I will be moving the PostgreSQL database to a similar configuration as SS. It will be interested to compare them then. To PostgreSQL developers, thank you for a great product! Avi -- Avi Schwartz avi@CFFtechnologies.com
Avi, > I understand why it will not be implemented with overloaded functions. > Is there a possibility to allow this only for functions that are not > overloaded? Unfortunately, no. There's simply no way for the database to tell the difference between a function call relying on defaults, and one with the wrong parameters. SQL Server's approach with defaults works *because* SQL Server does not support overloaded procedures. > The SQL function solution is really not going to help in > my case since the function builds a select statement dynamically based > on which parameters have a non-null value. The number of parameters is > something like 12 or 13 and the control on which parameters are set is > determined by a complex combination of program logic and user > selections. What I did to solve this problem was to force all > variables to be initialized to null and then set the non-null ones > before the call to the function. This sounds like a good solution to me. BTW, named parameters for PostgreSQL Functions are on the to-do list, but I don't think anyone is currently working on them. > very large tables (some of our tables are > 5M rows) :-) . What makes > it more impressive is the fact that SS runs on a 4 CPU machine with 2 > GB of memory while PostgreSQL on a single CPU machine with 384M memory > running SuSE 8.2. In the near future I will be moving the PostgreSQL > database to a similar configuration as SS. It will be interested to > compare them then. That's a very nice testimonial! Thanks. BTW, you will probably wish to join the PGSQL-Performance mailing list to make sure that you can tune your PostgreSQL database properly. -- Josh Berkus Aglio Database Solutions San Francisco