GreenPlum 获取建表语句函数实现

array_position

• 返回数组指定元素所在的位置，未匹配到返回0

create or replace function array_position(arrayint integer[], elementint integer, times integer default 1)
 returns integer
 language plpgsql
as $function$
/* 作者 : v-yuzhenc
 * 功能 : 返回数组指定元素所在的位置，未匹配到返回0
 * arrayint : 数组
 * element : 指定元素
 * times  : 第几次出现的位置
 * */
declare 
	p_times int := 0;
	p_result int := 0;
begin
	if array_length(arrayint,1) is null then 
		return p_result;
	end if;
	for i in 1..array_length(arrayint,1) loop  
		if arrayint[i] = elementint then 
			p_times := p_times + 1;
		end if;
		if p_times = times then
			return i;
		end if;
	end loop;
	return p_result;
end;
$function$
;

get_ddl

• 功能 : 给定表名(区分大小写)，返回当前表名的建表语句，备注语句

create or replace function get_ddl(
	 schematable varchar
	,getmode varchar default 'table'::varchar
	,newtablename varchar default null
)
 returns text
 language plpgsql
as $function$
/* 作者 : v-yuzhenc
 * 功能 : 给定表名(区分大小写)，返回当前表名的建表语句，备注语句
 * 		默认当前模式，其他模式请加 模式.表名
 * schematable : schemaname.tablename或者tablename
 * getmode : 默认table(获取表的建表语句)
 * 		view(获取视图的建视图语句)
 * 		viewtable(获取视图对应的建表语句)
 * newtablename : 以指定新表名返回建表语句，默认与原表名相同
 * */
declare 
	p_tablename varchar;
	p_schemaname varchar := user::varchar(64);
	p_newtablename varchar := newtablename;
	p_result text := null;
	p_array varchar[];
begin
	--校验getmode是否正确,不正确直接向外抛异常
	if getmode not in ('table','view','viewtable') then 
		raise exception '参数2必须为table、view或者viewtable！';
	end if;
	--如果传参为null直接抛出异常
	if schematable is null then 
		raise exception '表名或视图名不能为空！';
	end if;
	--含有多个点时，直接抛出异常
	if instr(schematable,'.',1,2) <> 0 then 
		raise exception '表名或视图名输入不正确！';
	end if;

	--解析schematable
	p_array := string_to_array(schematable,'.');
	if p_array[2] is null then
		p_tablename := p_array[1];
	else 
		p_tablename := trim(p_array[2]);
		p_schemaname := trim(p_array[1]);
	end if;
	p_newtablename := coalesce (p_newtablename,p_tablename);
	if getmode in ('table','viewtable') then
		if getmode = 'table' and not exists (select 1 from pg_tables where tablename = p_tablename and schemaname = p_schemaname) then 
			raise exception '%.%表不存在！',p_schemaname,p_tablename;
		elsif getmode = 'viewtable' and not exists (select 1 from pg_views where viewname = p_tablename and schemaname = p_schemaname) then
			raise exception '%.%视图不存在！',p_schemaname,p_tablename;
		end if;
		select 
			'drop table if exists "'||p_newtablename||'";'||
			chr(10)||'create table "'||p_newtablename||'" ('||
			chr(10)||
			string_agg(chr(9)||
				case when attnum = 1 then ' ' else ',' end||
				'"'||c.attname||'" '||  --字段名
				format_type(c.atttypid, c.atttypmod)||  --字段类型
				coalesce (' default '||substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128),'')||  --字段默认值
				case when c.attnotnull = true then ' not null' else ' null' end,chr(10) order by c.attnum
			)||
			--主键约束
			coalesce (chr(10)||chr(9)||',primary key ('||g.prikey||')','')||
			chr(10)||')'||
			--压缩信息
			coalesce(' with ( '||chr(10)||chr(9)||' '||array_to_string(a.reloptions,chr(10)||chr(9)||',')||chr(10)||')','') ||
			--分布策略
			case when e.policytype = 'r' then ' distributed replicated;' when e.policytype = 'p' then coalesce(' distributed by ('||
			string_agg(case when array_position(string_to_array(array_to_string(e.distkey::int2[],','),',')::int[],c.attnum::int,1) <> 0 then '"'||c.attname||'"' end,',' order by string_to_array(array_to_string(e.distkey::int2[],','),',')::int[])||
			');',' distributed randomly;') else ' distributed randomly;' end||
			--表备注（注释）
			coalesce(chr(10)||'comment on table "'||p_newtablename||'" is '''||replace(h.description,'''','''''')||''';','')||
			--字段备注（注释）
			coalesce(chr(10)||string_agg(case when f.description is not null then 'comment on column "'||p_newtablename||'"."'||c.attname||'" is '''||replace(f.description,'''','''''')||''';' end,chr(10) order by c.attnum),'')
		into p_result
		from pg_class a 
		inner join pg_namespace b 
		on (a.relnamespace = b.oid)
		inner join pg_attribute c 
		on (a.oid = c.attrelid)
		left join pg_attrdef d 
		on (c.attrelid = d.adrelid and c.attnum = d.adnum)
		left join gp_distribution_policy e 
		on (a.oid = e.localoid)
		left join pg_description f 
		on (a.oid = f.objoid and c.attnum = f.objsubid)
		left join (
		    select d.indrelid
		        ,string_agg('"'||c.attname||'"',',' order by c.attnum) prikey
		    from pg_class a, pg_namespace b, pg_attribute c, pg_index d 
		    where a.relnamespace = b.oid
		        and a.oid = c.attrelid
		        and a.oid = d.indrelid
		        and d.indisprimary = true
		        and c.attnum = any(d.indkey)
		        and a.relname = p_tablename
			    and b.nspname = p_schemaname  
			 group by d.indrelid
		) g 
		on (a.oid = g.indrelid)
		left join pg_description h 
		on (a.oid = h.objoid and h.objsubid = 0)
		where c.attnum > 0
			and not c.attisdropped
			and a.relname = p_tablename
			and b.nspname = p_schemaname
		group by b.nspname,a.relname,a.reloptions,e.policytype,h.description,g.prikey;
	else
		if getmode = 'view' and not exists (select 1 from pg_views where viewname = p_tablename and schemaname = p_schemaname) then
			raise exception '%.%视图不存在！',p_schemaname,p_tablename;
		end if;
		select 
			' CREATE OR REPLACE VIEW "'||p_newtablename||'" AS '||chr(10)||d.definition||
			--表备注（注释）
			coalesce(chr(10)||'comment on view "'||p_newtablename||'" is '''||replace(h.description,'''','''''')||''';','')||
			--字段备注（注释）
			coalesce(chr(10)||string_agg(case when f.description is not null then 'comment on column "'||p_newtablename||'"."'||c.attname||'" is '''||replace(f.description,'''','''''')||''';' end,chr(10) order by c.attnum),'')
		into p_result
		from pg_class a 
		inner join pg_namespace b 
		on (a.relnamespace = b.oid)
		inner join pg_attribute c 
		on (a.oid = c.attrelid)
		left join pg_description f 
		on (a.oid = f.objoid and c.attnum = f.objsubid)
		inner join pg_views d
		on (b.nspname = d.schemaname and a.relname = d.viewname)
		left join pg_description h 
		on (a.oid = h.objoid and h.objsubid = 0)
		where d.viewname = p_tablename
			and d.schemaname = p_schemaname
		group by a.relname,d.definition,h.description;
	end if;
	return p_result;
end;
$function$
;

测试

案例准备

drop table if exists tmp;
create table tmp (
	 id numeric(21)
	,name varchar(100)
	,age int 
	,update_time timestamp default current_timestamp
) with (
	 appendonly=true 
	,compresstype=zstd
	,compresslevel=3
)
distributed by (id);
comment on table tmp is '测试临时表';
comment on column tmp.id is '标识';
comment on column tmp.name is '名称';
comment on column tmp.age is '年龄';
comment on column tmp.update_time is '更新时间';

drop table if exists tmp1;
create table tmp1 (
	 id numeric(21) primary key
	,name varchar(100)
	,age int 
) distributed replicated;

drop table if exists tmp2;
create table tmp2 (
	 id numeric(21)
	,name varchar(100)
	,age int 
) distributed randomly;


create or replace view tmp3 as
select * from tmp;
comment on view tmp3 is '测试临时视图';
comment on column tmp3.id is '标识';
comment on column tmp3.name is '名称';
comment on column tmp3.age is '年龄';
comment on column tmp3.update_time is '更新时间';

结果

select get_ddl('tmp');
select get_ddl('tmp','table');

select get_ddl('tmp1');
select get_ddl('tmp1','table');

select get_ddl('tmp2');
select get_ddl('tmp2','table');

select get_ddl('tmp3','view');

select get_ddl('tmp3','viewtable');

select get_ddl('tmp3');