How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration?

Question

I have a function with 5 parameters,

• arguments p1, and p2 are mandatory
• arguments p3, p4, and p5 are optional and have a DEFAULT set

The function looks like this,

CREATE FUNCTION test_func(
  p1 int,
  p2 int,
  p3 int DEFAULT 3,
  p4 int DEFAULT 4,
  p5 int DEFAULT 5
)
  RETURNS int AS
$$
  SELECT p5;
$$ LANGUAGE sql;

I can invoke it like this,

SELECT test_func(1,2,3,4,5);
 test_func 
-----------
         5

But I would like to allow p3 and p4 to default and invoke it like this,

SELECT test_func(1,2,5);

Is there a way to specify the value for the last optional parameter (p5) without giving values to the preceding ones (p3, p4)? I don't want to hard-code default values for the p3, p4 in the call for test_func, because I may need to change their default values in the future.

Answer 1

You can simply supply the fifth argument with Mixed Notation using the => to separate the parameter name from the argument,

SELECT test_func(1,2,3,p5=>5);
test_func 
-----------
         5
(1 row)

From the docs on mixed notation with the one caveat (from the docs),

Named and mixed call notations currently cannot be used when calling an aggregate function (but they do work when an aggregate function is used as a window function).