小工具-Greenplum数据库中AO表和非AO表获取

Report Content

Issue: *
Copyright Infringment Spam Invalid Contents Broken Links

Your Name: *

Your Email: *

Details: *

Submit Report

--执行说明
/*该脚本是用来获取数据库中每个 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

*/