Writing a function in SQL to loop through a date range in a UDF
13,486
Solution 1
No need for functions:
select dhcp.singleday(a::date, a::date + 1)
from generate_series(
'2012-11-24'::date,
'2012-12-03',
'1 day'
) s(a)
This will work for any date range. Not only an inside month one.
Solution 2
Simple plpgsql function:
CREATE OR REPLACE FUNCTION f_machine_gun_sally(date, date)
RETURNS void AS
$func$
DECLARE
d date := $1;
BEGIN
LOOP
PERFORM dhcp.singleday(d, d+1);
d := d + 1;
EXIT WHEN d > $2;
END LOOP;
END
$func$ LANGUAGE plpgsql;
- Use
PERFORM
when you want don't care about the return value. - You can just add an
integer
to adate
to increment. Month or year boundaries are irrelevant this way.
Author by
user7980
Updated on July 22, 2022Comments
-
user7980 almost 2 years
I am trying to automate the process of running a
PLPGSQL
function for a range of dates.
Typically I have to run the following code that generates a single table per day per function call:SELECT dhcp.singleday('2012-11-24'::date, '2012-11-25'::date); SELECT dhcp.singleday('2012-11-25'::date, '2012-11-26'::date); SELECT dhcp.singleday('2012-11-26'::date, '2012-11-27'::date); SELECT dhcp.singleday('2012-11-27'::date, '2012-11-28'::date); SELECT dhcp.singleday('2012-11-28'::date, '2012-11-29'::date); SELECT dhcp.singleday('2012-11-29'::date, '2012-11-30'::date); SELECT dhcp.singleday('2012-11-30'::date, '2012-12-01'::date); SELECT dhcp.singleday('2012-12-01'::date, '2012-12-02'::date); SELECT dhcp.singleday('2012-12-02'::date, '2012-12-03'::date); SELECT dhcp.singleday('2012-12-03'::date, '2012-12-04'::date);
Is there a good way to automate this sort of thing with a simple loop or function for an arbitrary date range?
I am thinking it might be hard to handle the cases of going month to month so I suppose it is better assume the date range is for a single month.
-
Eric Brooks about 9 yearsI found this to be helpful, but when I ran it, I found that rather than returning all the columns that would be returned if I run my function for just a single date, it instead returns a single column with all the original columns concatenated together. Is there a way to fix this?