PostgreSQL教程--实现类似于MySQL的show create table功能

背景

在MySQL数据库中，可以通过show create table查询表的create sql 语句，
但在PostgreSQL数据库中，没有类似的命令，但可以通过function 来实现

效果

在PostgreSQL 12中测试
实现后，使用示例如下：

select showcreatetable('public','xxl_job_qrtz_trigger_log');

结果如下：

 CREATE TABLE xxl_job_qrtz_trigger_log ( 
   id  integer  0  PRI  not null,
   job_group  integer  0    not null   comment 执行器主键ID,
   job_id  integer  0    not null   comment 任务，主键ID,
   executor_address  character varying(255)  255    null,
   executor_handler  character varying(255)  255    null,
   executor_param  text  0    null,
   executor_sharding_param  character varying(20)  20    null,
   executor_fail_retry_count  integer  0    not null   comment 失败重试次数,
   trigger_time  date  0  UNI  null,
   trigger_code  integer  0    not null   comment 调度-结果,
   trigger_msg  text  0    null,
   handle_time  date  0    null,
   handle_code  integer  0  UNI  not null   comment 执行-状态,
   handle_msg  text  0    null,
   alarm_status  smallint  0    not null   comment 告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败,
   batchid  bigint  0    not null   comment 运行批次,
   offsetstart  bigint  0    not null   comment 时间周期起始值,
   offsetend  bigint  0    not null   comment 时间周期结束值,
   CONSTRAINT pk_xxl_job_qrtz_trigger_log PRIMARY KEY (id),
   CONSTRAINT i_trigger_time UNIQUE(trigger_time,handle_code),
   CONSTRAINT i_handle_code UNIQUE(trigger_time,handle_code)
 ); 

CREATE INDEX pk_xxl_job_qrtz_trigger_log ON xxl_job_qrtz_trigger_log USING btree (id);,
CREATE INDEX i_trigger_time ON xxl_job_qrtz_trigger_log USING btree (trigger_time);,
CREATE INDEX i_handle_code ON xxl_job_qrtz_trigger_log USING btree (handle_code);

COMMENT ON COLUMNxxl_job_qrtz_trigger_log.job_group IS  '执行器主键ID',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.job_id IS  '任务，主键ID',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.executor_fail_retry_count IS  '失败重试次数',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.trigger_code IS  '调度-结果',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.handle_code IS  '执行-状态',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.alarm_status IS  '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.batchid IS  '运行批次',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.offsetstart IS  '时间周期起始值',
COMMENT ON COLUMNxxl_job_qrtz_trigger_log.offsetend IS  '时间周期结束值'

show create table函数代码

showcreatetable函数

CREATE OR REPLACE FUNCTION showcreatetable(namespace character varying, tablename character varying)
  RETURNS character varying AS
  
$BODY$
declare 
tableScript character varying default '';
 
begin
-- columns
tableScript:=tableScript || ' CREATE TABLE '|| tablename|| ' ( '|| chr(13)||chr(10) || array_to_string(
  array(
select '   ' || concat_ws('  ',fieldName, fieldType, fieldLen, indexType, isNullStr, fieldComment ) as column_line
from (
select a.attname as fieldName,format_type(a.atttypid,a.atttypmod) as fieldType,(case when atttypmod-4>0 then atttypmod-4 else 0 end) as fieldLen,
(case  when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'PRI' 
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'UNI' 
when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'FRI' 
 else '' end) as indexType,
(case when a.attnotnull=true then 'not null' else 'null' end) as isNullStr,
' comment ' || col_description(a.attrelid,a.attnum) as fieldComment 
from pg_attribute a where attstattarget=-1 and attrelid = (select c.oid from pg_class c,pg_namespace n where c.relnamespace=n.oid and n.nspname =namespace and relname =tablename)

) as string_columns
),','||chr(13)||chr(10)) || ',';
 
 
-- 约束
tableScript:= tableScript || chr(13)||chr(10) || array_to_string(
array(
	select concat('   CONSTRAINT ',conname ,c ,u,p,f)   from (
		select conname,
		case when contype='c' then  ' CHECK('|| ( select findattname(namespace,tablename,'c') ) ||')' end  as c  ,
		case when contype='u' then  ' UNIQUE('|| ( select findattname(namespace,tablename,'u') ) ||')' end as u ,
		case when contype='p' then ' PRIMARY KEY ('|| ( select findattname(namespace,tablename,'p') ) ||')' end  as p  ,
		case when contype='f' then ' FOREIGN KEY('|| ( select findattname(namespace,tablename,'u') ) ||') REFERENCES '|| 
		(select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select findattname(namespace,tablename,'u') ) ||')' end as  f
		from pg_constraint c
		where contype in('u','c','f','p') and conrelid=( 
			select oid  from pg_class  where relname=tablename and relnamespace =(
			select oid from pg_namespace where nspname = namespace
			)
		 )
	) as t  
) ,',' || chr(13)||chr(10) ) || chr(13)||chr(10) ||' ); ';

-- indexs 
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
 
 
-- 
/** **/
--- 获取非约束索引 column
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
	array(
		select 'CREATE INDEX ' || indexrelname || ' ON ' || tablename || ' USING btree '|| '(' || attname || ');' from (
		 SELECT 
		    i.relname AS indexrelname ,  x.indkey, 
		    
		    ( select array_to_string (
			array( 
				select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )
 
			     ) 
		     ,',' ) )as attname
		    
		   FROM pg_class c
		   JOIN pg_index x ON c.oid = x.indrelid
		   JOIN pg_class i ON i.oid = x.indexrelid
		   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
		   WHERE  c.relname=tablename and i.relname not in
			  ( select constraint_name from information_schema.key_column_usage  where  table_name=tablename  )
		)as t
) ,','|| chr(13)||chr(10));
			
 
-- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键';
tableScript:= tableScript || chr(13)||chr(10) || chr(13)||chr(10) || array_to_string(
array(
SELECT 'COMMENT ON COLUMN' || tablename || '.' || a.attname ||' IS  '|| ''''|| d.description ||''''
FROM pg_class c
JOIN pg_description d ON c.oid=d.objoid
JOIN pg_attribute a ON c.oid = a.attrelid
WHERE c.relname=tablename
AND a.attnum = d.objsubid),','|| chr(13)||chr(10)) ;
 
return tableScript;
 
end
$BODY$ LANGUAGE plpgsql;

findattname函数

查询表某一个约束的列名并转换为一个字符串

CREATE OR REPLACE FUNCTION findattname(namespace character varying, tablename character varying, ctype character varying)
  RETURNS character varying as $BODY$
 
declare
tt oid ;
aname character varying default '';
 
begin
       tt := oid from pg_class where relname= tablename and relnamespace =(select oid from pg_namespace  where nspname=namespace) ;
       aname:=  array_to_string(
		array(
		       select a.attname  from pg_attribute  a 
				where a.attrelid=tt and  a.attnum   in (		
				select unnest(conkey) from pg_constraint c where contype=ctype 
				and conrelid=tt  and array_to_string(conkey,',') is not null  
			) 
		),',');
	
	return aname;
end
$BODY$ LANGUAGE plpgsql;