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




*/