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
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.
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.
_start_date timestamp without time zone default now()
_company integer default 2
_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 + (‘ 1day’)::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’
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.
What are we going to learn Please use the Data set and the table structure from the previous post (previous post). In a previous post, we did the similar kind
You will be capable of studying about PostgreSQL XML fields How to insert the XML to the DB field Retrieve data from the XML field Scenario: Sometimes we need to
When you delete records from a PostgreSQL table, the table size may not immediately reduce. This is because PostgreSQL employs a technique called MVCC (Multi-Version Concurrency Control), which keeps old