Pavel Stehule wrote, and then Tom Lane committed (on 16th of July), patch which adds to PostgreSQL support for functions with variable number of arguments.
These function are called “VARIADIC", and up until now writing such a function was impossible.
You could overcome this limitation by:
- defining a lot of functions with the same name, but different sets
- passing array to function
New patch makes creation of such functions a breeze:
Support "variadic" functions, which can accept a variable number of arguments so long as all the trailing arguments are of the same (non-array) type. The function receives them as a single array argument (which is why they have to all be the same type). It might be useful to extend this facility to aggregates, but this patch doesn't do that. This patch imposes a noticeable slowdown on function lookup --- a follow-on patch will fix that by adding a redundant column to pg_proc. Pavel Stehule
As for last note from commit log – of course there was second patch which made the lookup faster.
How do these functions work? It's pretty simple:
CREATE FUNCTION get_powers(IN base INT4, VARIADIC powers INT4, OUT POWER INT8) RETURNS SETOF INT8 AS $$ DECLARE i int4; BEGIN FOR i IN SELECT generate_subscripts(powers, 1) LOOP POWER := base ^ powers[i]; RETURN NEXT; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;
This function takes following arguments:
- obligatory integer, which will be base for power
- 1+ integers which will be use as power factor
It will return recordset, with only 1 column (named power), which will contain base raised to given power:
# SELECT * FROM get_powers(2, 3); POWER ------- 8 (1 ROW) # SELECT * FROM get_powers(2, 3, 5, 10); POWER ------- 8 32 1024 (3 ROWS)
If I will not give any powers, exception will be raised:
# SELECT * FROM get_powers(2); ERROR: FUNCTION get_powers(INTEGER) does NOT exist LINE 1: SELECT * FROM get_powers(2); ^
Now, if only I could pass variadic to pl/perl in sane way, I could have sql version of sprintf() 🙂