PostgreSQL DB Recursive Function – Holiday Calendar

Can we do recursive in a DB function

Simply yes. We can do very comprehensive recursive function with the help of any DB function like we used in any other programming language like java, C++, .Net. I personally tested with MySQL, SQL Server, PostgreSQL. Here i try to explain you a sample with the help of a postgres DB.

What you learn from here

• Postgres function
• Recursive functions
• Create a Table in postgress
• Create a function in postgress
• Calling a postgres function
• Default parameters
• Coalesce function
• Postgres Casting
• Postgres Interval

Scenario

Simple Holiday Calendar

You have a company holiday calendar which maintain by you. Our requirement is, need to pass a calendar date and then you need to take the next working day based on the holidays in the table.

Example: According to the calendar MAY 25,26 set as a holiday. If you pass 25th MAY as your date, function will return you the 27th MAY as the next working day. For this one, i used a recursive function and I’ll simply explain you how it works

Setup your environment

Table:

CREATE TABLE holiday_calender (
	hcl_id bigserial NOT NULL,
	hcl_country integer,
	hcl_company integer,
	hcl_date timestamp NULL,
	hcl_description varchar(500) NULL,	
	hcl_active int4 NOT NULL,
	CONSTRAINT holiday_calender_pkey PRIMARY KEY (hcl_id)
);

Sample Data:

INSERT INTO holiday_calender (hcl_country,hcl_company,hcl_date,hcl_description,hcl_active) VALUES
	 (2,1,'2022-05-21 00:00:00','Holiday 1',1),
	 (2,1,'2022-05-22 00:00:00','Holiday 2',1),
	 (2,1,'2022-05-23 00:00:00','Holiday 3',1),
	 (2,1,'2022-05-25 00:00:00','Holiday 4',1),
	 (2,1,'2022-05-26 00:00:00','Holiday 5',1),
	 (2,1,'2022-05-27 00:00:00','Holiday 6',1);

How Data looks now:

Now the Function:

CREATE OR REPLACE FUNCTION set_fun_compairecalender(_start_date timestamp without time zone DEFAULT now(), _company integer DEFAULT 2, _country integer DEFAULT 1)
 RETURNS timestamp without time zone
 LANGUAGE plpgsql
AS $function$
declare 
	dte timestamp;
begin
	
  select coalesce(hcl_date,'1900-01-01') into dte from holiday_calender hc where  hc.hcl_country = _country  and 
  hc.hcl_company  = _company  and hc.hcl_date::date = _start_Date::date;
	
  if coalesce(dte,'1900-01-01') <> '1900-01-01' then 
    -- Recursive function call at here
    select set_fun_compaireCalender into dte from set_fun_compaireCalender(dte::date + (' 1 day')::interval,_company,     _country);

  end if;

  return coalesce(dte,_start_Date)::date;

end
$function$
;

Sample Outputs:

Execute:

select * from set_fun_compairecalender('2022-05-21',1,2);

Output:

2022-05-24 00:00:00.000

According to the holiday calendar that we setup, 21,22 and 23 are holidays. due to that system identified its next working day as 24th MAY.

Function Explanation

How the function works

• You passing the start date as the first parameters to check any holidays available for the given date
• Inside the function, it check any availability of a holiday for the particular date through the query as

	select coalesce(hcl_date,'1900-01-01') into dte from holiday_calender hc where  hc.hcl_country = _country  and 
	hc.hcl_company  = _company  and hc.hcl_date::date = _start_Date::date;

• Query will check for the holiday
• If a date not found through the query, it will return ‘1900-01-01’ as the output and
• If found, it will return the calendar date it found.
• Then function will assign the received date in to the dte variable.
• As the next step, function will check the output dte value is ‘1900-01-01’ or not.

	if coalesce(dte,'1900-01-01') <> '1900-01-01' then 
	--	_start_Date = dte::date + (' 1 day')::interval;
		select set_fun_compaireCalender into dte from set_fun_compaireCalender(dte::date + (' 1 day')::interval,_company, _country);
	end if;

• If it is not equal to ‘1900-01-01’, that mean, selected date is a holiday. So there is a possibility that next date also become a holiday.
• To verify that, we again call the same function inside the if condition. (Recursive start from here)
• You can see that, before we pass the date to the same function again, we added a 1 day to the received output value.
• Then next time when we call the same function again, system will check for the received date + day 1. (basically the next date)
• If that return ‘1900-01-01’ as the output, that mean, there is no holiday for that date. if date found, simply it call the same function again as a recursive to check the next date also a holiday by using the same if condition.
• Like wise system will continuously check for the sequential holidays based on to the date that we passed as the start date.

Explain other sub areas

Default parameters:

CREATE OR REPLACE FUNCTION set_fun_compairecalender(_start_date timestamp without time zone DEFAULT now(), _company integer DEFAULT 2, _country integer DEFAULT 1)

Default parameters are help you to set initial values to your parameters without passing any values. According to the above function you can see there are three parameters available.

1. _start_date timestamp without time zone default now()
2. _company integer default 2
3. _country integer default 1

All these three parameters are contain default parameters. Normally when your calling a function you must pass all the parameters to execute the function. Otherwise it gives you an error say that not enough parameters available.

select * from set_fun_compairecalender('2022-05-21',1,2);

But with the help of the default parameters, you do not need to pass any values. you can just run this function as follow

select * from set_fun_compairecalender();

coalesce

coalesce(hcl_date,‘1900-01-01’)

Coalesce check your first parameter value is null or not. If it is null, it return you the second value as the out put. you can pass any number of parameters to this function as coalsce(param1, param2, param3, param4)

Casting:

hc.hcl_date::date

You can cast any data type using :: as in above sample. Example, if you need to convert varchar value to integer ‘1’::integer, if you need to convert a integer into a character varying, simply 8::character varying.

Interval:

dte::date + (‘ 1 day’)::interval

If you need to add an hour, minute, day, or any date time-related value, you can use the above command. The above sample will convert the dte variable into a date and then add a 1 Date to the variable and return the new date’

(action_date::timestamp + (coalesce(threshold,0) || ' minutes')::interval)

The above sample will explain to you that, the variable actiondate will be added by minutes which is based on the variable “threshhold” value. In this example, actiondate and the threshold are both variables that you can pass as a parameter.

More Advance calendar, please use this one