用SQL编写函数以遍历UDF中的日期范围 [英] Writing a function in SQL to loop through a date range in a UDF
本文介绍了用SQL编写函数以遍历UDF中的日期范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试自动运行日期范围内的PLPGSQL
函数的过程.
通常,我必须运行以下代码,每个函数调用每天生成一个表:
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.
推荐答案
无需功能:
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.
这篇关于用SQL编写函数以遍历UDF中的日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文