Thread: Function with DEFAULT arguments
Hello,
I'm trying to use the DEFAULT option to pass parameters to the arguments of a
function.
When I call that function, how can I change the default value of some
arguments and leave as default the value of other arguments? In other words, is
there a way to 'call' the arguments by their names so to specify which should
have their default value changed?
Here's a toy example, a function that takes three strings as arguments and
concatenate them:
CREATE FUNCTION test_default(string1 text default 'a', string2 text default
'b', string3 text default 'c') RETURNS text AS $$
BEGIN
RETURN string1 || string2 || string3;
END;
$$ language 'plpgsql';
-- Only default args:
SELECT test_default(); --> abc
-- With custom values:
SELECT test_default('X', 'Y', 'Z'); --> XYZ
-- Now, how can I leave as default the 1st and 3rd argument (string1 and
string3) and change only the second one (string2)? I would like to do something
like:
SELECT test_default(string2= 'Y'); -- To return 'aYb'
And in general, are there any examples/documentation that show how to use the
option default?
I'm using postgresql 8.4.2 on Windows XP.
Many thanks
Dario
On Fri, Mar 12, 2010 at 05:28:57PM +0100, dario.ber@libero.it wrote:
> Hello,
>
> I'm trying to use the DEFAULT option to pass parameters to the arguments of a
> function.
> When I call that function, how can I change the default value of some
> arguments and leave as default the value of other arguments? In other words, is
> there a way to 'call' the arguments by their names so to specify which should
> have their default value changed?
>
> Here's a toy example, a function that takes three strings as arguments and
> concatenate them:
>
> CREATE FUNCTION test_default(string1 text default 'a', string2 text default
> 'b', string3 text default 'c') RETURNS text AS $$
> BEGIN
> RETURN string1 || string2 || string3;
> END;
> $$ language 'plpgsql';
>
> -- Only default args:
> SELECT test_default(); --> abc
>
> -- With custom values:
> SELECT test_default('X', 'Y', 'Z'); --> XYZ
>
> -- Now, how can I leave as default the 1st and 3rd argument (string1 and
> string3) and change only the second one (string2)? I would like to do something
> like:
You can't unless you're on PostgreSQL 9.0:
http://www.depesz.com/index.php/2009/11/17/waiting-for-8-5-named-function-arguments/
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
"dario.ber@libero.it" <dario.ber@libero.it> writes:
> I'm trying to use the DEFAULT option to pass parameters to the arguments of a
> function.
> When I call that function, how can I change the default value of some
> arguments and leave as default the value of other arguments?
You can only omit arguments from right to left, so basically what this
requires is some foresight while choosing the function's argument order.
regards, tom lane
Tom Lane wrote: > "dario.ber@libero.it" <dario.ber@libero.it> writes: >> I'm trying to use the DEFAULT option to pass parameters to the arguments of a >> function. >> When I call that function, how can I change the default value of some >> arguments and leave as default the value of other arguments? > > You can only omit arguments from right to left, so basically what this > requires is some foresight while choosing the function's argument order. > > regards, tom lane > What do you think about allowing 'default' as a parameter the way we do when inserting a record? So if a param has a default value, the function call may look like: SELECT foo(default, 'hello', 43) Scott Bailey
Scott Bailey <artacus@comcast.net> writes:
> Tom Lane wrote:
>> You can only omit arguments from right to left, so basically what this
>> requires is some foresight while choosing the function's argument order.
> What do you think about allowing 'default' as a parameter the way we do
> when inserting a record?
Seems like a nonstarter because of overloading considerations --- what
are you going to do if there are multiple possible matches?
In any case, the match-arguments-by-name solution available in PG 9.0
seems a whole lot superior to complicating positional match even more.
regards, tom lane
>----Messaggio originale----
>Da: tgl@sss.pgh.pa.us
>Data: 12/03/2010 17.51
>A: "dario.ber@libero.it"<dario.ber@libero.it>
>Cc: <pgsql-general@postgresql.org>
>Ogg: Re: [GENERAL] Function with DEFAULT arguments
>
>"dario.ber@libero.it" <dario.ber@libero.it> writes:
>> I'm trying to use the DEFAULT option to pass parameters to the arguments of
a
>> function.
>> When I call that function, how can I change the default value of some
>> arguments and leave as default the value of other arguments?
>
>You can only omit arguments from right to left, so basically what this
>requires is some foresight while choosing the function's argument order.
>
> regards, tom lane
Thanks for replies!
Would it be a very horrible workaround to pass a single string to the function
which contains the user's parameters? This string then is parsed into the
individual arguments/defaults inside the function. In this way there is no need
to have arguments in any order.
Example using plpythonu:
CREATE OR REPLACE FUNCTION test_default(arg_string text) RETURNS text AS
$$
## List of pseudo-arguments the function can take
arg_1= 'arg_1'
arg_2= 'arg_2'
arg_3= 'arg_3'
## Convert the argument string to a dictionary
arg_dict= eval('{' + arg_string + '}')
## Retrieve user's parameters and assign defaults
try:
arg_1= arg_dict[arg_1]
except:
arg_1= 'A'
try:
arg_2= arg_dict[arg_2]
except:
arg_2= 'B'
try:
arg_3= arg_dict[arg_3]
except:
arg_3= 'C'
## Do something with the parameters
return('One: ' + arg_1 + '; Two: ' + arg_2 + '; Three: ' + arg_3)
$$
language 'plpythonu';
-- Execute with default 'pseudo-arguments' only:
SELECT test_default($$ $$);
--> One: A; Two: B; Three: C
-- With arg_2 as default:
SELECT test_default($$ arg_3:'z', arg_1:'x' $$);
--> One: x; Two: B; Three: z
All the best
Dario
2010/3/13 dario.ber@libero.it <dario.ber@libero.it>:
>>----Messaggio originale----
>>Da: tgl@sss.pgh.pa.us
>>Data: 12/03/2010 17.51
>>A: "dario.ber@libero.it"<dario.ber@libero.it>
>>Cc: <pgsql-general@postgresql.org>
>>Ogg: Re: [GENERAL] Function with DEFAULT arguments
>>
>>"dario.ber@libero.it" <dario.ber@libero.it> writes:
>>> I'm trying to use the DEFAULT option to pass parameters to the arguments of
> a
>>> function.
>>> When I call that function, how can I change the default value of some
>>> arguments and leave as default the value of other arguments?
>>
>>You can only omit arguments from right to left, so basically what this
>>requires is some foresight while choosing the function's argument order.
>>
>> regards, tom lane
>
> Thanks for replies!
> Would it be a very horrible workaround to pass a single string to the function
> which contains the user's parameters? This string then is parsed into the
> individual arguments/defaults inside the function. In this way there is no need
> to have arguments in any order.
>
> Example using plpythonu:
>
> CREATE OR REPLACE FUNCTION test_default(arg_string text) RETURNS text AS
> $$
> ## List of pseudo-arguments the function can take
> arg_1= 'arg_1'
> arg_2= 'arg_2'
> arg_3= 'arg_3'
>
> ## Convert the argument string to a dictionary
> arg_dict= eval('{' + arg_string + '}')
>
> ## Retrieve user's parameters and assign defaults
> try:
> arg_1= arg_dict[arg_1]
> except:
> arg_1= 'A'
> try:
> arg_2= arg_dict[arg_2]
> except:
> arg_2= 'B'
> try:
> arg_3= arg_dict[arg_3]
> except:
> arg_3= 'C'
>
> ## Do something with the parameters
> return('One: ' + arg_1 + '; Two: ' + arg_2 + '; Three: ' + arg_3)
> $$
> language 'plpythonu';
>
> -- Execute with default 'pseudo-arguments' only:
> SELECT test_default($$ $$);
> --> One: A; Two: B; Three: C
>
> -- With arg_2 as default:
> SELECT test_default($$ arg_3:'z', arg_1:'x' $$);
> --> One: x; Two: B; Three: z
>
> All the best
> Dario
what is sense of this?
It is a problem, because pg selection of adequate function is based on
type compatibility. and varchar isn't compatible with n - params -
mainly we don't see, what is content of some string (if there is one
parameter, or ten parameters).
If you need carry some unspecified number of parameters, then use
function with hstore type parameter.
Regards
Pavel Stehule
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>