分区表循环函数

CREATE OR REPLACE FUNCTION lw.loop_create_table(

    beg_date date,

    days integer)

  RETURNS integer AS

$BODY$

  DECLARE

    end_date date ;

    DECLARE quantity text :='false';

         DECLARE tab text;

    DECLARE sql_txt text;

    declare num int :=0;

   begin

   end_date:=beg_date+days;

   if beg_date>=end_date then

   RAISE NOTICE 'beg_date>=end_date %>%',beg_date,end_date;

   return '0';

   else

   loop

   end_date :=beg_date+num;

    num:=num+1;

   if num>days then

      exit;

   else

   tab:='lw_test_'||TO_CHAR(end_date,'YYYYMMDD');

   select  into tab tablename from test.pg_catalog.pg_tables where schemaname = 'lw' and tablename=tab;

   if FOUND THEN --判断分区表是否存在，存在就先删再建

   sql_txt :='DROP TABLE lw.'||tab||';';

   execute sql_txt;

  

   sql_txt :='create table lw.lw_test_'||TO_CHAR(end_date,'YYYYMMDD')||'

             (CONSTRAINT agg_cust_trade_day_'||TO_CHAR(end_date,'YYYYMMDD')||'_busi_date_check

              CHECK (busi_date >= '''||end_date||'''::date AND busi_date < '''||end_date+1||'''::date)          

             )INHERITS (lw.lw_test);';

    execute sql_txt;

   

   ELSE

   sql_txt :='create table lw.lw_test_'||TO_CHAR(end_date,'YYYYMMDD')||'

             (CONSTRAINT agg_cust_trade_day_'||TO_CHAR(end_date,'YYYYMMDD')||'_busi_date_check

              CHECK (busi_date >= '''||end_date||'''::date AND busi_date < '''||end_date+1||'''::date)          

             )INHERITS (lw.lw_test);';

    execute sql_txt;

         END IF;

    end if;

   end loop; 

   END IF;

   quantity :='sucess';

   return 0;

   end;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29050044/viewspace-2127445/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29050044/viewspace-2127445/