Re: generic return for functions - Mailing list pgsql-sql

From Avi Schwartz
Subject Re: generic return for functions
Date
Msg-id 54D7C9C2-944C-11D7-ACB0-000393AE5044@CFFtechnologies.com
Whole thread Raw
In response to Re: generic return for functions  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: generic return for functions
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: generic return for functions
Next
From: Josh Berkus
Date:
Subject: Re: PostgreSQL and industry