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