3

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.

3

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.