用于 UNION 大量表的 SQL 脚本

Question

在此示例中，我必须对从 2012-12-17 到 2012-10-30 的大量不相交的日表进行联合。这里的代码变得丑陋是片段:

CREATE table map 
with (appendonly=true, compresstype = quicklz)
AS
 SELECT * FROM final_map_12_17
    UNION ALL
 SELECT * FROM final_map_12_16
    UNION ALL
     SELECT * FROM final_map_12_15
    UNION ALL
     SELECT * FROM final_map_12_14
    UNION ALL
....
SELECT * FROM final_map_10_30;

我可以用序列或 PL/PGSQL 函数来做这种事情，而不是手动写出每个单独的选择吗？

Answer 1

您可以像这样在 plpgsql 函数中循环遍历日期范围:

create or replace function add_map(date_from date, date_to date)
returns void language plpgsql as $$
declare
    day date;
begin
    for day in 
        select generate_series(date_from, date_to, '1 day')
    loop
        execute 'insert into map select * from final_map_'|| 
            to_char(extract(month from day), '09')|| '_' || 
            to_char(extract(day from day), '09');
    end loop;
end; $$;

调用函数:

-- create table map (....);
select add_map('2012-11-30', '2012-12-02');

相当于:

insert into map select * from final_map_11_30;
insert into map select * from final_map_12_01;
insert into map select * from final_map_12_02;