PosgreSQ批量删除分区表\批量修改表属性

水深无声 2022-02-22 15:10 422阅读 0赞

CREATE Or replace FUNCTION del_tables(tblname varchar,sof int,eof int) RETURNS void AS $$
    DECLARE
        tmp VARCHAR(512);
    BEGIN
      FOR sof IN sof..eof LOOP
        tmp := 'DROP TABLE IF EXISTS '|| tblname||sof||';' ;
        RAISE NOTICE 'notice: %', tmp;
        EXECUTE 'DROP TABLE IF EXISTS '|| tblname||sof||';' ;
      END LOOP;
      RAISE NOTICE 'finished .....';
    END;
     
    $$ LANGUAGE plpgsql;
    
    
    select del_tables('tablename_p',0,16);

### 解释 ###

假设分区表是 tablename\_p1到 tablename\_p16，通过上面脚本可以一次性删除。

### 执行结果 ###

skynet=# select del_tables('tablename_p',0,16);
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p0;
    NOTICE:  table "tablename_p0" does not exist, skipping
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p1;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p2;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p3;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p4;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p5;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p6;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p7;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p8;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p9;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p10;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p11;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p12;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p13;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p14;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p15;
    NOTICE:  notice: DROP TABLE IF EXISTS tablename_p16;
    NOTICE:  finished .....
     del_tables 
    ------------

###  ###

其实可以通过excel生成删除语句，一次性执行就好了。没必要用这些脚本，只是为了学习一下存储过程。

## 批量修改表owner ##

DO $$DECLARE r record;
    BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'alter table '|| r.tablename ||' owner to snabu;';
    END LOOP;
    END$$;