Default values in function, arguments dependency, PostgreSQL

Question

I would like to write a function with two arguments, which would have dependency. Something like this:

    CREATE OR REPLACE FUNCTION years_compare(
        IN year1 integer DEFAULT date_part('year'::text, ('now'::text)::date),
        IN year2 integer DEFAULT year1 - 1)

...

When I call a function years_compare(2019), the second argument would have a value 2018. But how can I write a default value for the second argument?

Answer 1

CREATE OR REPLACE FUNCTION years_compare( IN year1 integer DEFAULT NULL,
                                          IN year2 integer DEFAULT NULL )

    year1 = COALESCE(year1, date_part('year'::text, ('now'::text)::date));
    year2 = COALESCE(year2, year1 - 1);
-- ...

Answer 2

Maybe using Postgres' support of polymorphism might be of use here.

First create the base function with both parameters.

CREATE FUNCTION years_compare(IN year1 integer,
                              IN year2 integer)
                RETURNS integer
AS
$$
BEGIN
  RETURN 1; --<replace by some calculated value>
END;
$$
LANGUAGE PLpgSQL;

Then create one function with only one parameter calling the base function with the first parameter and the calculated second parameter.

CREATE FUNCTION years_compare(IN year1 integer)
                RETURNS integer
AS
$$
BEGIN
  RETURN years_compare(year1, year1 - 1);
END;
$$
LANGUAGE PLpgSQL;

Finally create a parameterless version of the function calling the second one with a calculated parameter.

CREATE FUNCTION years_compare()
                RETURNS integer
AS
$$
BEGIN
  RETURN years_compare(date_part('year'::text, ('now'::text)::date)::integer);
END;
$$
LANGUAGE PLpgSQL;

Then you have three versions of the function with zero to two parameters.