Writing a function in SQL to loop through a date range in a UDF

Question

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.

Have you tried generate_series function?

Ihor Romanchenko
– Ihor Romanchenko

2013-01-19 10:16:51 +00:00
Commented Jan 19, 2013 at 10:16 — Ihor Romanchenko
– Ihor Romanchenko, Commented Jan 19, 2013 at 10:16

Clodoaldo Neto · Accepted Answer · 2013-01-19 13:05:38Z

26

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.

answered Jan 19, 2013 at 13:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Brooks Over a year ago

I 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?

Erwin Brandstetter · Accepted Answer · 2013-01-19 10:36:21Z

8

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 a date to increment. Month or year boundaries are irrelevant this way.

answered Jan 19, 2013 at 10:36

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Writing a function in SQL to loop through a date range in a UDF

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related