postgresql - PostgreSQL plpgsql：生成系列不适用于 12 月

Question

我正在尝试根据往返月份动态生成一系列表格，但使用我拥有的代码，它不会为 12 月份生成。感谢您的所有帮助。

CREATE OR REPLACE FUNCTION "gracenote"."fn_gn_tv_schedule_tables_test" ()  RETURNS void
  VOLATILE
AS $dbvis$
DECLARE
      today_date TEXT;
      partitionn TEXT;
      partition_check TEXT;
      part_table_from DATE;
      part_table_to DATE;
      part_from DATE;
      part_to DATE;
      constrnt TEXT;
      indx TEXT;
      createStmts TEXT;
      create_query TEXT;
      index_query TEXT;
      delete_query TEXT;
BEGIN
    today_date := to_char(CURRENT_DATE,'YYYYMM');
    partition_check := 'gn_tv_schedule_test' || '_' || today_date;
    partitionn := 'gracenote.gn_tv_schedule_test' || '_' || today_date;
    part_from := DATE(TO_CHAR( CURRENT_DATE,'YYYY-MM') || '-01');
    part_to := DATE(TO_CHAR( CURRENT_DATE + INTERVAL '1 month', 'YYYY-MM' ) || '-01');
    part_table_from := DATE (TO_CHAR( CURRENT_DATE + INTERVAL '1 month','YYYY-MM') || '-01');
    part_table_to := DATE(TO_CHAR( CURRENT_DATE - INTERVAL '4 month', 'YYYY-MM' ) || '-01');
    constrnt := 'gn_tv_schedule_cnstrt_test_' || to_char(CURRENT_DATE, 'YYYYMM');
    indx := 'gn_tv_schedule_idx_test_' || to_char(CURRENT_DATE, 'YYYYMM');


    FOR create_query, index_query IN SELECT
            'CREATE TABLE gracenote.gn_tv_schedule_test_' || TO_CHAR(d, 'YYYYMM') || ' (CONSTRAINT gn_tv_schedule_cnstrt_test_' || TO_CHAR(d, 'YYYYMM') || ' check (( date >= date ''' 
            || TO_CHAR(d, 'YYYY-MM-01') || ''') and (date < date ''' || TO_CHAR(d + INTERVAL '1 month','YYYY-MM-01') || '''))) INHERITS (gracenote.gn_tv_schedule_panda);',
            'CREATE INDEX gn_tv_schedule_idx_test_' || TO_CHAR(d, 'YYYYMM') || ' ON gracenote.gn_tv_schedule_test_' || TO_CHAR(d, 'YYYYMM') || '(date);'
        FROM generate_series(part_table_to, part_table_from, '1 month' ) AS d
    LOOP
           IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition_check) THEN

              EXECUTE create_query;
              EXECUTE index_query;
           END IF;
    END LOOP;

END;
$dbvis$ LANGUAGE plpgsql

输出是从“gn_tv_schedule_test_201807”到“gn_tv_schedule_test_201811”创建的表。'gn_tv_schedule_test_201812' 没有被创建。

标签： postgresqlplpgsqlgenerate-series