参考博文：https://blog.csdn.net/xiaofengtoo/article/details/84395199
修复了其函数中的bug，支持生成包含：字段（支持数组类型字段）、约束、索引（支持生成唯一索引，支持全类型索引）在内的建表语句。
生成的sql指定scheme为：【sch_租户id】，不同scheme生成规则或者不需要指定scheme直接修改下相关代码即可使用。
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 "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