Waiting for 8.4 – variadic functions

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
    of arguments
  • 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:

  1. obligatory integer, which will be base for power
  2. 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() 🙂

One thought on “Waiting for 8.4 – variadic functions”

  1. Indeed, as you mentioned, it is possible to do this by passing an array. And probably in a case such as described in the example, people should still be using an array. IMO, the syntax like
    select * from get_powers(2, 3, 5, 10);
    is less clear about whats being done than
    select * from get_powers(2, array[3, 5, 10]);

    The more compelling reason to have variadic functions is to allow a default value for an omitted parameter!
    I.e. as our code evolves, we often add some new parameters to the function, but would like to not rewrite other code that refers to the said function – when we call the old way, with the parameter omitted, we want some default value.
    What we see here so far is not going to solve the problem for us, even if all the new parameters are of the same type, say strings. Indeed, because there’s an exception raised when the argument was omitted makes it impossible.

Comments are closed.