greenplum获取数据字典

CREATE OR REPLACE FUNCTION int2vector2text(i int2vector) RETURNS text AS $$
        BEGIN
                RETURN i;
        END;
$$ LANGUAGE plpgsql;

create or replace function get_index_col(attrelid text, indexcolsnum text , indnatts smallint )
    returns text
as $$
	index_cols=''
	index_num2=''
	#index_cols=None
	for i in range(0,indnatts):
		num=int(i)
		index_num=indexcolsnum.split(' ')[num]
		index_num1=int(index_num)
		get_table_ind_col="select attname from PG_ATTRIBUTE where attrelid= %s and attnum= %s "% (attrelid,index_num1);
		rv_table_ind_col=plpy.execute(get_table_ind_col)
		colname=str(rv_table_ind_col[0]['attname'])
		if num == indnatts -1:
			index_cols+=colname
		else:
			index_cols+=colname+','
		index_num2 +=str(num)
	return str(index_cols)
$$ LANGUAGE plpythonu;

create or replace function get_destributed_col(localoid text)
    returns text
as $$
	destributed_cols=''
	colnum=0
	for num in range(1,10):
		get_destributed_colnum="select ATTRNUMS[%s] as attnum from gp_distribution_policy where localoid= %s "%(num,localoid);
		rv_destributed_colnum=plpy.execute(get_destributed_colnum)
		if rv_destributed_colnum[0]['attnum'] == None:
			return str(destributed_cols)
		else:
			colnum=int(rv_destributed_colnum[0]['attnum'])
			get_destributed_cols="select attname from PG_ATTRIBUTE where attrelid= %s and attnum= %s "% (localoid,colnum);
			rv_destributed_col=plpy.execute(get_destributed_cols)
			destributed_cols+=str(rv_destributed_col[0]['attname'])+','
	return str(destributed_cols)
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION schema_name.f_get_array_position(v_char text , v_array_string text[])
  returns integer 
as 
$$
DECLARE
    ARRAY_LEN integer;
BEGIN
  ARRAY_LEN=array_upper(v_array_string,1);
	for i in 1..ARRAY_LEN loop
		if v_char=v_array_string[i] then 
			return i;
	  end if;
	end loop;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION schema_name.f_get_gp_tabinfo()
  returns 
table(
gp_schema text,
gp_tableName text,
gp_tableDesc text,
gp_colName text,
gp_colDesc text,
gp_colType text,
gp_colMaxlen text,
gp_colIsnull text,
gp_colDefault text,
gp_colOrder text,
gp_ind_cols text,
gp_und_cols text,
gp_pk_cols text,
gp_colIsdistricols text,
gp_colIsPartition text,
gp_tabPartitionType text,	
gp_colIsSubPartition text,
gp_tabSubPartitionType text,
col_type text)
  language sql as
$$

with gp_table_info as (
select 
gp_schema,
gp_tableName,
gp_tableDesc,
gp_colName,
gp_colDesc,
case gp_colType 
	when 'int8' then 'bigint'
	when 'int4' then 'integer'
	when 'int2' then 'smallint'
	when 'timestamp' then 'timestamp without time zone'
	when 'timestamptz' then 'timestamp with time zone'
	when 'time' then 'time without time zone'
	when 'timetz' then 'time with time zone'
	when 'float4' then 'real'
	when 'float8' then 'double precision'
	when 'date' then 'date' 
    when 'varchar' then 'varchar'
	when 'bpchar' then 'char'
	when 'text' then 'text'
	when 'numeric' then 'numeric'
	when 'interval' then 'interval'
end as gp_colType,
CASE   WHEN gp_colType='numeric' THEN (atttypmod - 4) / 65536||','||(atttypmod - 4) % 65536
	   WHEN gp_colType IN ('bpchar','varchar') THEN CAST(atttypmod - 4 AS TEXT)
	   WHEN gp_colType IN ('timestamp','timestamptz','time','timetz') THEN CAST(pg_truetypmod AS TEXT)
ELSE null end as gp_colMaxlen,
gp_colIsnull,
gp_colDefault,
gp_colIsdistribution,
gp_colIsdistricols,
gp_colIsindex,
gp_colIsunique,
gp_colIspk,
gp_indexCols,
gp_colOrder,
gp_colIsPartition,
gp_tabPartitionType,	
gp_colIsSubPartition,
gp_tabSubPartitionType,
gp_colType as col_type
from (
SELECT 
	    B.SCHEMANAME AS gp_schema ,
		B.TABLENAME AS gp_tableName ,
		CAST(OBJ_DESCRIPTION(RELFILENODE,'pg_class') AS VARCHAR ) AS gp_tableDesc,
		t1.attname AS gp_colName ,
		t3.description as gp_colDesc,
		t2.typname AS gp_colType ,
		case when t1.attnotnull=true then 'Y' else null end as gp_colIsnull,
		t4.adsrc as gp_colDefault,
        case when t5.attrnums is not null then 'Y' else null end as gp_colIsdistribution,
		case when t5.attrnums is not null then trim(get_destributed_col(T5.LOCALOID),',') else null end as gp_colIsdistricols,
		case when t6.indisunique=true or t6.indisprimary=true then 'T' 
			 when t6.indkey is not null then 'Y'
			 else null end as gp_colIsindex,
		case when t6.indisunique=true and t6.indisprimary=true then 'T' when t6.indisunique=true then 'Y' else null end as gp_colIsunique, 
		case when t6.indisprimary=true then 'Y' else 'N' end as gp_colIspk, 
	    t1.attnum as gp_colOrder,
		CASE WHEN t2.typtype = 'd' THEN t2.typbasetype ELSE t1.atttypid END pg_truetypid,
		CASE WHEN t2.typtype = 'd' THEN t2.typtypmod ELSE t1.atttypmod END  pg_truetypmod,
		case when p.partitionlevel='0' then 'Y' else 'N' end AS gp_colIsPartition      ,
		p.partitiontype AS gp_tabPartitionType		 ,	
		case when p.partitionlevel='1' then 'Y' else 'N' end AS gp_colIsSubPartition   ,
		p.partitiontype AS gp_tabSubPartitionType ,
        t2.typtype	,
		T1.atttypmod,
		A.relhassubclass,
		case when t6.indkey is not null then get_index_col(t1.attrelid , int2vector2text(t6.indkey) , t6.indnatts ) else null end as gp_indexCols
		
FROM PG_CLASS A
----PG_CLASS.RELKIND：r = ordinary table（普通表）, i = index（索引）, S = sequence（序列）, v = view（视图）, m = materialized view（物化视图）, c = composite type（复合类型）, t = TOAST table（TOAST 表）, f = foreign table（外部表）
--PG_CLASS.relhassubclass：如果有(或者曾经有)任何继承的子表，为真。
INNER JOIN PG_TABLES B
ON A.RELNAME = B.TABLENAME
INNER JOIN PG_ATTRIBUTE t1
ON A.OID = t1.ATTRELID
AND t1.ATTNUM >0
AND t1.ATTISDROPPED <> 't'
--PG_ATTRIBUTE.ATTISDROPPED：字段已经被删除
--PG_ATTRIBUTE.ATTNUM：字段数目。普通字段是从 1 开始计数的
left join pg_type t2  on t1.atttypid = t2.oid -- 类型
--pg_type.typtype：对于基础类型是b，对于复合类型是c (比如，一个表的行类型)。对于域类型是d，对于枚举类型是e， 对于伪类型是p，对于范围类型是r
left join "pg_catalog"."pg_description" t3 on  t1.attrelid=t3.objoid and t3.objsubid = t1.attnum -- 注释
left join pg_attrdef  t4 on t4.adrelid = t1.attrelid AND t4.adnum = t1.attnum -- 默认值
left join gp_distribution_policy  t5 on t5.localoid = t1.attrelid and t1.attnum = any(t5.attrnums) -- 分布键
left join pg_index t6 on t6.indrelid=t1.attrelid and t1.attnum = any(t6.indkey)  -- 索引，主键等
--获取分区信息
left join (
	select pc.*,p.partitiontype from (SELECT par.schemaname,par.tablename,par.partitionlevel,par.partitiontype
	  FROM pg_partitions par group by par.schemaname,par.tablename,par.partitionlevel,par.partitiontype)p
 left join (
			select par.schemaname,par.tablename,par.partitionlevel,par.columnname 
	          from pg_partition_columns par 
		  group by par.schemaname,par.tablename,par.partitionlevel,par.columnname
		  )pc
	    on p.schemaname =pc.schemaname
	   and p.tablename = pc.tablename
	   and p.partitionlevel =pc.partitionlevel
)p
on b.schemaname =p.schemaname
and b.tablename = p.tablename
and t1.ATTNAME = p.columnname

where b.SCHEMANAME ='schema_name'  AND A.RELKIND ='r'
)t
where gp_tableName not like '%prt%'
--and gp_tableName = tablename
order by gp_schema asc ,gp_tableName asc  ,gp_colIspk desc ,gp_colOrder asc

)
select 
cast(gp_schema as text ),
cast(gp_tableName as text ),
cast(gp_tableDesc as text ),
cast(gp_colName as text ),
cast(gp_colDesc as text ),
cast(gp_colType as text ),
cast(gp_colMaxlen as text ),
cast(gp_colIsnull as text ),
cast(gp_colDefault as text ),
cast(gp_colOrder as text ),
cast(gp_ind_cols as text ),
cast(gp_und_cols as text ),
cast(gp_pk_cols as text ),
cast(gp_colIsdistricols as text ),
cast(gp_colIsPartition as text ),
cast(gp_tabPartitionType as text ),	
cast(gp_colIsSubPartition as text ),
cast(gp_tabSubPartitionType as text ),
cast(col_type as text )
from 
(
	  select 
			 a.gp_schema,
			 a.gp_tableName,
			 a.gp_tableDesc,
			 a.gp_colName,
			 a.gp_colDesc,
			 a.gp_colType,
			 a.gp_colMaxlen,
			 a.gp_colIsnull,
			 a.gp_colDefault,
			 a.gp_colOrder,
			 a.gp_colIsdistricols,
			 a.gp_colIsPartition,
			 a.gp_tabPartitionType,	
			 a.gp_colIsSubPartition,
			 a.gp_tabSubPartitionType,
			 a.col_type,
			 b.gp_ind_cols,
			 c.gp_und_cols,
			 d.gp_pk_cols
	    from gp_table_info A
   left join (select gp_schema,gp_tableName,gp_colName,string_agg(gp_indexCols,'|') as gp_ind_cols
                from gp_table_info b where b.gp_colIsindex='Y' group by gp_schema,gp_tableName,gp_colName )b
          on b.gp_schema = a.gp_schema
		 and b.gp_tableName = a.gp_tableName
		 and b.gp_colName = a.gp_colName
   left join (select gp_schema,gp_tableName,gp_colName,string_agg(gp_indexCols,'|') as gp_und_cols
                from gp_table_info b where b.gp_colIsunique='Y' group by gp_schema,gp_tableName,gp_colName ) c
          on c.gp_schema = a.gp_schema
		 and c.gp_tableName = a.gp_tableName
		 and c.gp_colName = a.gp_colName	
   left join (select gp_schema,gp_tableName,gp_colName,string_agg(gp_indexCols,'|') as gp_pk_cols
                from gp_table_info b where b.gp_colIspk='Y' group by gp_schema,gp_tableName,gp_colName ) d
          on d.gp_schema = a.gp_schema
		 and d.gp_tableName = a.gp_tableName
		 and d.gp_colName = a.gp_colName
)t		 
group by 
gp_schema,
gp_tableName,
gp_tableDesc,
gp_colName,
gp_colDesc,
gp_colType,
gp_colMaxlen,
gp_colIsnull,
gp_colDefault,
gp_colOrder,
gp_colIsdistricols,
gp_colIsPartition,
gp_tabPartitionType,	
gp_colIsSubPartition,
gp_tabSubPartitionType,
col_type,
gp_ind_cols,
gp_und_cols,
gp_pk_cols

$$ ;