Waiting for 8.5 – Named function arguments

Pavel Stehule – hero for everybody writing stored procedures, wrote, and later Tom Lane committed patch which adds named arguments for functions:

Log Message:
-----------
Support use of function argument names to identify which actual arguments
match which function parameters.  The syntax uses AS, for example
funcname(value AS arg1, anothervalue AS arg2)
 
Pavel Stehule

So, let's see how it works.

CREATE OR REPLACE FUNCTION test(
    IN  x TEXT,
    IN  y TEXT,
    OUT z TEXT
) AS $$
BEGIN
    z:= regexp_replace( x, y, '', 'g' );
    RETURN;
END;
$$ LANGUAGE plpgsql;

This is pretty simple function that replaces removes parts of string that match given regexp:

# SELECT test('Hubert Depesz Lubaczewski', '[a-z]+( |$)');
 test
──────
 HDL
(1 ROW)

Now, I can call this function as:

# SELECT test('Hubert Depesz Lubaczewski' AS x, '[a-z]+( |$)' AS y);

Which doesn't make it very special, but the fact that the arguments are named means I can reorder them:

# SELECT test('[a-z]+( |$)' AS y, 'PostgreSQL Is Absolutely Great' AS x);
      test
────────────────
 PostgreSQL IAG
(1 ROW)

Which is cool, but not something that I would call life-changing. The real power in here comes when we'll combine this patch with previous patch (also by Pavel), which added default values for functions.

As you perhaps remember, it had the limitation that you couldn't use default value for 1st argument, but provide argument for 2nd.

But now – it's no longer a problem:

CREATE OR REPLACE FUNCTION test(
    IN  x TEXT DEFAULT 'DefaultX',
    IN  y TEXT DEFAULT 'DefaultY',
    IN  z TEXT DEFAULT 'DefaultZ',
    OUT o TEXT
) AS $$
BEGIN
    o := printf( 'x=[%] , y=[%] , z=[%]', x, y, z );
    RETURN;
END;
$$ LANGUAGE plpgsql;

( printf function provided by Alvaro Herrera )

And now I can:

# SELECT test();
                    test
────────────────────────────────────────────
 x=[DefaultX] , y=[DefaultY] , z=[DefaultZ]
(1 ROW)
 
# SELECT test('a');
                test
─────────────────────────────────────
 x=[a] , y=[DefaultY] , z=[DefaultZ]
(1 ROW)
 
# SELECT test('a', 'b');
             test
──────────────────────────────
 x=[a] , y=[b] , z=[DefaultZ]
(1 ROW)
 
# SELECT test('a', 'b', 'c');
         test
───────────────────────
 x=[a] , y=[b] , z=[c]
(1 ROW)
 
# SELECT test('c' AS z);
                test
─────────────────────────────────────
 x=[DefaultX] , y=[DefaultY] , z=[c]
(1 ROW)

Of course it should work in all languages that support argument names. For example pl/Python:

CREATE OR REPLACE FUNCTION pytest(
    x TEXT,
    y TEXT,
    z TEXT
) RETURNS TEXT AS $$
RETURN "Python: x = [%s], y = [%s], z = [%s]" % (x, y, z)
$$ LANGUAGE plpythonu;

And call:

# SELECT pytest('depesz' AS y, 'hubert' AS x, 'lubaczewski' AS z);
                        pytest
───────────────────────────────────────────────────────
 Python: x = [hubert], y = [depesz], z = [lubaczewski]
(1 ROW)

This is very cool. Great work Pavel.

One thought on “Waiting for 8.5 – Named function arguments”

  1. Hey Pavel, stop that ! You’ve just removed another reason to keep code outside of postgres ! How can I keep pretending that RDBMs are just “dumb data stores” now ?

    /me is dancing around joyfully 🙂

Comments are closed.