ADB for postgresql 查看表建表语句，查看表DLL语句

在MySQL中可以通过show create table schema_name.table_name;的方式查看建表语句。

在oracle中可以使用select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA_NAME') from dual; 查看建表语句。

而ADB for postgresql 中无对应的函数或语法提供给我们查看表的定义语句，所以就自己试着建立一个函数实现，代码如下：

create or replace function petl.show_table_ddl(tab_schema varchar(10),tbl_name varchar(100))
returns text
as
$$
declare
	tabl_name varchar(200);
	result_all text;
	result_create varchar;
	result_options varchar;
	result_dist varchar;
	result_part varchar;
begin
	tabl_name := tab_schema || tbl_name;
	--CREATE
	select 'CREATE TABLE ' || tabl_name || chr(13) ||' ( ' || string_agg('"' || tmp1.attname || '"' || tmp1.a || ' ' || tmp1.b || tmp1.c || tmp1.d || chr(13),',') || ')' || chr(13) as CT
	from
	(
		select a.attname
			  ,format_type(a.atttypid,a.atttymod) a
			  ,case when a.attnotnull then ' not null ' else ' ' end b
			  ,case when a.atthasdef then ' default ' else ' ' end c
			  ,case when pg_catalog.pg_get_expr(d.adbin,d.adrelid,true) is null then ' ' else pg_catalog.pg_get_expr(d.adbin,d.adrelid,true) end d
		from pg_attribute a
		left join pg_attrdef d
		on d.adrelid = a.attrelid
		and d.adnum = a.attnum
		where a.attrelid = tabl_name::regclass
		and a.attnum > 0
		and a.attisdropped = 'f'
	) tmp1
	into result_create;
	
	--OPTIONS
	select 'WITH (appendonly='
			|| case when columnstore then 'true' else 'false' end
			|| case when blocksize > 8192 then ',blocksize=' || blocksize else '' end
			|| case when compresstype is not null then
					case when compresslevel > 0 then (',compresstype=' || compresstype || ',compresslevel=' || compresslevel)
					else (',compresstype=' || compresstype ) end
			   else '' end
			|| case when checksum then ',checksum=true' else ',checksum=false' end
			|| case when columnstore then ',orientation=column' else ',orientation=row' end
			|| ')' || chr(13) as options
	
	from pg_catalog.pg_appendonly
	where relid = tabl_name::regclass
	into result_options;
	
	if result_options is null then result_options := '';
	end if;
	
	--DISTRIBUTED BY
	select
			concat('DISTRIBUTED BY (',string_agg(ccc.attname,','),')',chr(13))::varchar(100) dist
	from (
		select aa.oid
			  ,regexp_split_to_table(bb.distkey::varchar(50),' ')::varchar(10) att
		from pg_class aa
		left join gp_distribution_policy bb
		on bb.localoid = aa.oid
		left join pg_inherits hh  --关联此表目的是过滤掉有继承关系的分区子表
		on hh.inhrelid = aa.oid
		where aa.oid = tabl_name::regclass
		and bb.localoid = tabl_name::regclass
		and hh.inhrelid is null  --过滤掉有继承关系的分区子表，不查看分区子表的表结构，只查母表的结构
	) aaa
	left join pg_attribute ccc
	on ccc.attrelid = aaa.oid
	and cast( ccc.attnum as varchar(10)) = aaa.att
	where ccc.attrelid = tabl_name::regclass
	and ccc.attnum > 0
	into result_dist;
	
	--PARTITION
	select pg_get_partition_def(tbl_name::regclass,true)
	into result_part;
	
	if result_part is null then result_part :='';
	end if;
	result_all := result_create || result_options || result_dist || result_part || ';';
	return result_all;
	
end
$$
language plpgsql;