--执行阐明
/*该脚本是用来获取数据库中每个 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, skippingDROP TABLEpostgres=# drop table if exists tab_naotable;NOTICE:  table "tab_naotable" does not exist, skippingDROP TABLEpostgres=# postgres=# create  temporary table tab_aotable(table_oid oid,table_name text,aotable text,cond char(1)) distributed by (table_oid);CREATE TABLEpostgres=# 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*/