关于greenplum:小工具Greenplum数据库中AO表和非AO表获取

– 执行阐明
/* 该脚本是用来获取数据库中每个 schema 下是否是 AO 表，如果有 AO 表, 将会存储于长期表
*tab_aotable 中, 如果是非 AO 表, 那么将会存储于长期表 tab_naotable 中，因为存储非 AO
* 表和 AO 表都是存储于长期表中的，因而在会话退出后，长期表将会主动销毁，如果须要获取，
* 请从新执行以下语句

*/


drop table if exists tab_aotable;
drop table if exists tab_naotable;

create  temporary table tab_aotable(table_oid oid,table_name text,aotable text,cond char(1)) distributed by (table_oid);

create  temporary table tab_naotable(table_oid oid,table_name text,naotable text,cond char(1)) distributed by (table_oid);


create or replace function f_get_aotable()
returns void 
as
$$
declare
    v_list_toid oid;
    v_list_tname text;
    v_sql text;
    v_is_ao_tablename text;
    v_table_oid oid;
    v_table_name text;
    v_is_ao_table text;
    cur1 CURSOR FOR 
                SELECT a.oid,
                       c.nspname ||'.' || b.tablename
                FROM pg_class a,
                     pg_tables b,
                     pg_namespace c
                WHERE a.relname = b.tablename
                  AND c.oid = a.relnamespace
                  AND a.relname !~ '^pg|^gp|^_|^sql';
begin
    open cur1;
    loop
        fetch cur1 into v_list_toid,v_list_tname;
        exit when not found;
        v_sql = 'SELECT relid,
                        t1.nspname||''.''||t1.tablename,
                        t1.reloptions
                 FROM pg_appendonly t,
                    (SELECT a.oid,
                            a.reloptions,
                            b.tablename,
                            c.nspname
                     FROM pg_class a,
                          pg_tables b,
                          pg_namespace c
                    WHERE a.relname = b.tablename
                    AND   c.oid = a.relnamespace
                    AND   a.relname !~  ''^pg|^gp|^_|^SQL'') t1
                WHERE t.relid = t1.oid
                AND   t1.oid = ' ||v_list_toid ;

        execute v_sql into v_table_oid,v_table_name,v_is_ao_table;
        if v_table_oid is not null then
            insert into tab_aotable values(v_table_oid,v_table_name,v_is_ao_table,'1');
        else
            insert into tab_naotable values(v_list_toid,v_list_tname,'no ao table','0');
        end if;
    end loop;
        raise notice 'PLPGSQL Exec Successfully';
    close cur1;
end;
$$
language plpgsql;

/* 以下为执行示例

– 删除和创立存储 AO 和非 AO 表的长期表

postgres=# drop table if exists tab_aotable;
NOTICE:  table "tab_aotable" does not exist, skipping
DROP TABLE
postgres=# drop table if exists tab_naotable;
NOTICE:  table "tab_naotable" does not exist, skipping
DROP TABLE
postgres=# 
postgres=# create  temporary table tab_aotable(table_oid oid,table_name text,aotable text,cond char(1)) distributed by (table_oid);
CREATE TABLE
postgres=# 
postgres=# create  temporary table tab_naotable(table_oid oid,table_name text,naotable text,cond char(1)) distributed by (table_oid);
CREATE TABLE

– 创立函数此处略，仅作为查问曾经创立好该函数

postgres=# \df f_get_aotable();
                            List of functions
 Schema |     Name      | Result data type | Argument data types |  Type  
--------+---------------+------------------+---------------------+--------
 public | f_get_aotable | void             |                     | normal
(1 row)

– 执行函数

postgres=# select * from f_get_aotable();
NOTICE:  PLPGSQL exec successfully
 f_get_aotable 
---------------
 
(1 row)

– 查看 AO 表

postgres=# SELECT * FROM tab_aotable ;
 table_oid |           table_name           |      aotable      | cond 
-----------+--------------------------------+-------------------+------
     33337 | public.tab_sales_1_prt_returns | {appendonly=true} | 1
     33324 | public.tab_sales_1_prt_sales   | {appendonly=true} | 1
     33227 | public.tab_t2                  | {appendonly=true} | 1
     33314 | public.tab_sales               | {appendonly=true} | 1
(4 rows)

– 查看非 AO 表

postgres=# select * from tab_naotable ;
 table_oid |       table_name       |  naotable   | cond 
-----------+------------------------+-------------+------
     32783 | s2.tab_product_57      | no ao table | 0
     32799 | s2.tab_product_60      | no ao table | 0
     32815 | s2.tab_product_64      | no ao table | 0
     32831 | s2.tab_product_68      | no ao table | 0


*/