参考博文：https://blog.csdn.net/xiaofengtoo/article/details/84395199

修复了其函数中的bug，支持生成包含：字段（支持数组类型字段）、约束、索引（支持生成唯一索引，支持全类型索引）在内的建表语句。

生成的sql指定scheme为：【sch_租户id】，不同scheme生成规则或者不需要指定scheme直接修改下相关代码即可使用。

原

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) ;
	-- select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=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 VOLATILE
  COST 100;
 

改

CREATE OR REPLACE FUNCTION "public"."findattname"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "ctype" varchar)
  RETURNS "pg_catalog"."varchar" 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 and nspowner=(select datdba from pg_database where datname=dbinstancename) ) ;      
       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 VOLATILE
  COST 100

原

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|| ' ( '|| array_to_string(
  array(
select concat( c1, c2, c3, c4, c5, c6 ) as column_line
from (
  select column_name || ' ' || data_type as c1,
    case when character_maximum_length > 0 then '(' || character_maximum_length || ')' end as c2,
    case when numeric_precision > 0 and numeric_scale < 1 then '(' || numeric_precision || ')' end as c3,
    case when numeric_precision > 0 and numeric_scale > 0 then '(' || numeric_precision || ', ' || numeric_scale || ')' end as c4,
    case when is_nullable = 'NO' then ' NOT NULL' end as c5,
    case when column_default is not Null then ' DEFAULT' end || ' ' || replace(column_default, '::character varying', '') as c6
  from information_schema.columns
  where table_name = tablename
  -- and table_schema=namespace
  order by ordinal_position
) as string_columns
),' , ') ||',' ;
 
 
-- 约束
tableScript:= tableScript || array_to_string(
array(
	select concat(' CONSTRAINT ',conname ,c ,u,p,f)   from (
		select conname,
		case when contype='c' then  ' CHECK('|| consrc ||')' 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 nspowner=(select datdba from pg_database where datname=namespace)
			)
		 )
	) as t  
) ,',' ) || ' ); ';
	
-- 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 || array_to_string(
	array(
		select 'CREATE UNIQUE 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
) ,',' );
			
 
-- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键';
tableScript:= tableScript || 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),',') ;
 
return tableScript;
 
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 

改

CREATE OR REPLACE FUNCTION "public"."showcreatetable"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "tenantid" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare 
tableScript character varying default '';
tableNum int2 ;
 
begin
-- check db extist
tableNum:= count(*)    from pg_class  where relname=tablename and relnamespace =(
            select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=dbinstancename) and nspname = namespace
            );
IF tableNum=0  then 
return '' ;
end if;
-- columns
tableScript:=tableScript || ' CREATE TABLE IF NOT EXISTS '|| '"sch_'||tenantid||'".'||tablename|| ' ( '|| array_to_string(
  array(
select concat( c1, c2, c3, c4, c5, c6 ) as column_line
from (
  select '"'||column_name||'"' || ' ' || case when data_type='ARRAY' then ltrim(udt_name,'_')||'[]' else data_type end as c1,
    case when character_maximum_length > 0 then '(' || character_maximum_length || ')' end as c2,
    case when numeric_precision > 0 and numeric_scale < 1 then null end as c3,
    case when numeric_precision > 0 and numeric_scale > 0 then null end as c4,
    case when is_nullable = 'NO' then ' NOT NULL' end as c5,
    case when column_default is not Null then ' DEFAULT' end || ' ' || replace(column_default, '::character varying', '') as c6
  from information_schema.columns
  where table_name = tablename
    and table_catalog=dbinstancename
  and table_schema=namespace
  order by ordinal_position
) as string_columns
),' , ') ||',' ;
 
 
-- 约束
tableScript:= tableScript || array_to_string(
array(
    select concat(' CONSTRAINT ','"'||conname||'"' ,c ,u,p,f)   from (
        select conname,
        case when contype='c' then  ' CHECK('|| consrc ||')' end  as c  ,
        case when contype='u' then  ' UNIQUE('|| ( select findattname(dbinstancename,namespace,tablename,'u') ) ||')' end as u ,
        case when contype='p' then ' PRIMARY KEY ('|| ( select findattname(dbinstancename,namespace,tablename,'p') ) ||')' end  as p  ,
        case when contype='f' then ' FOREIGN KEY('|| ( select findattname(dbinstancename,namespace,tablename,'u') ) ||') REFERENCES '|| 
        (select p.relname from pg_class p where p.oid=c.confrelid )  || '('|| ( select findattname(dbinstancename,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 nspowner=(select datdba from pg_database where datname=dbinstancename) and nspname = namespace
            )
         )
    ) as t  
) ,',' ) || ' ); '; 
 
-- 
/** **/
--- 获取非约束索引 column
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
tableScript:= tableScript || array_to_string(
    array(
        select 'CREATE '||case when is_unique_index=true then 'UNIQUE INDEX' else 'INDEX' end ||'"' || indexrelname ||'"' || ' ON ' || '"sch_'||tenantid||'".'||tablename|| ' USING '||index_type|| '(' || 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, x.indisunique is_unique_index,am.amname index_type
            
           FROM pg_class c
           JOIN pg_index x ON c.oid = x.indrelid
           JOIN pg_class i ON i.oid = x.indexrelid
             join pg_am am on am.oid = i.relam
           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  )
                and c.relnamespace =(
            select oid from pg_namespace where nspowner=(select datdba from pg_database where datname=dbinstancename) and nspname = namespace
            )
        )as t
) ,'' );
            

 
return tableScript;
 
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
 

测试

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