On 4th of December Peter Eisentraut committed patch by Pavel Stehule (with Peters tweaks) which adds default values for function arguments:
Default values for function arguments Pavel Stehule, with some tweaks by Peter Eisentraut
Well, the functionality is pretty obvious, so let's see how to use it in real code. To do so I'll write a simple functions to convert given integer to arbitrary base.
First, function which will return array of “digits":
CREATE OR REPLACE FUNCTION _int_to_base( IN NUMBER INT8, IN base INT4 DEFAULT 16 ) RETURNS INT4[] AS $$ DECLARE reply INT4[] := '{}'; use_number INT8 := NUMBER; BEGIN LOOP reply := ARRAY[use_number % base]::INT4[] || reply; use_number := use_number / base; EXIT WHEN use_number <= 0; END LOOP; RETURN reply; END; $$ LANGUAGE plpgsql;
It is just helper function, so I prefixed its name with _.
How does it work?
# SELECT _int_to_base(312312,8); _int_to_base ----------------- {1,1,4,1,7,7,0} (1 ROW) # SELECT _int_to_base(312312,10); _int_to_base --------------- {3,1,2,3,1,2} (1 ROW)
Pretty simple. Now, note the “DEFAULT 16" in function declaration. It means that the function will convert to base16 if base was not supplied:
# SELECT _int_to_base(312312); _int_to_base --------------- {4,12,3,15,8} (1 ROW) # SELECT _int_to_base(255); _int_to_base -------------- {15,15} (1 ROW)
Nice.
Having this we can move to function that will convert integer to string containing arbitrary digits:
CREATE OR REPLACE FUNCTION int_to_base( IN NUMBER INT8, IN digits TEXT[] DEFAULT '{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f}', IN separator TEXT DEFAULT '' ) RETURNS TEXT AS $$ DECLARE temp_array INT4[] := _int_to_base( NUMBER, array_length( digits, 1 ) ); output_array TEXT[] := '{}'; i INT4; BEGIN FOR i IN SELECT generate_subscripts(temp_array, 1) LOOP output_array := output_array || digits[ temp_array[i] + 1 ]; END LOOP; RETURN array_to_string( output_array, separator ); END; $$ LANGUAGE plpgsql;
As you can see the function takes 3 arguments: integer to be converted, array of digits to be used, and separator to be put between digits in output string.
Please also note, that when the function will be called with less arguments it will assume to be use hexadecimal digits, and empty separator:
# SELECT int_to_base(255); int_to_base ------------- ff (1 ROW) # SELECT int_to_base(255, '{a,b,c}'); int_to_base ------------- baabba (1 ROW) # SELECT int_to_base(255, '{a,b,c}', ':'); int_to_base ------------- b:a:a:b:b:a (1 ROW)
There is slight issue though – one cannot call the function and provide value for second optional argument without giving value to first. So if I'd like to get hexadecimal number, but with digits separate
# SELECT int_to_base(255, ','); ERROR: array VALUE must START WITH "{" OR dimension information LINE 1: SELECT int_to_base(255, ','); ^
But I have to provide all arguments that are before the one that I want to give value to. In our case I have to:
# SELECT int_to_base(255, '{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f}', ','); int_to_base ------------- f,f (1 ROW)
In previous versions of PostgreSQL you could emulate default values by writing separate functions with overloaded argument list – but this approach looks much nicer.