[GENERAL] Function with DEFAULT arguments

2010-03-12 Thread dario....@libero.it 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread hubert depesz lubaczewski 
On Fri, Mar 12, 2010 at 05:28:57PM +0100, dario@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: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

R: Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread dario....@libero.it 
Ok, thanks for the quick reply and the link!

Dario

Messaggio originale
Da: dep...@depesz.com
Data: 12/03/2010 17.38
A: dario@libero.itdario@libero.it
Cc: pgsql-general@postgresql.org
Ogg: Re: [GENERAL] Function with DEFAULT arguments

On Fri, Mar 12, 2010 at 05:28:57PM +0100, dario@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: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread Tom Lane 
dario@libero.it dario@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread Scott Bailey 

Tom Lane wrote:

dario@libero.it dario@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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread Tom Lane 
Scott Bailey arta...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread dario....@libero.it 
Messaggio originale
Da: t...@sss.pgh.pa.us
Data: 12/03/2010 17.51
A: dario@libero.itdario@libero.it
Cc: pgsql-general@postgresql.org
Ogg: Re: [GENERAL] Function with DEFAULT arguments 

dario@libero.it dario@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function with DEFAULT arguments

2010-03-12 Thread Pavel Stehule 
2010/3/13 dario@libero.it dario@libero.it:
Messaggio originale
Da: t...@sss.pgh.pa.us
Data: 12/03/2010 17.51
A: dario@libero.itdario@libero.it
Cc: pgsql-general@postgresql.org
Ogg: Re: [GENERAL] Function with DEFAULT arguments

dario@libero.it dario@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general