postgreSQL生成建表语句-2.0

pg没有提供⽣成创建字段的函数，需要⾃⼰查表实现。其他包括约束、索引等都有系统函数，在其基础上加⼯使⽤即可。⽀持⽣成包含：字段（⽀持数组类型字段）、约束、索引（⽀持⽣成唯⼀索引，⽀持全类型索引）在内的建表语句。

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

1 CREATE OR REPLACE FUNCTION \"public\".\"showcreatetable\"(\"dbinstancename\" varchar, \"namespace\" varchar, \"tablename\" varchar, \"tenantid\" varchar) 2 RETURNS \"pg_catalog\".\"varchar\" AS $BODY$ 3 declare

4 tableScript character varying default ''; 5 indexScripe character varying default ''; 6 constraintScripe character varying default ''; 7 p_namespace character varying default ''; 8 p_tablename character varying default ''; 9 tableNum int2 ; 10 constraintIds oid[];

11 v_conname varchar; 12

13 begin

14 p_namespace := namespace; 15 p_tablename := tablename; 16 -- check db extist

17 tableNum := count(*) 18 from pg_class

19 where relname = p_tablename 20 and relnamespace = ( 21 select oid

22 from pg_namespace

23 where nspname = p_namespace 24 );

25 IF tableNum = 0 then 26 return '' ; 27 end if; 28 -- columns

29 tableScript := tableScript || ' CREATE TABLE IF NOT EXISTS ' || '\"sch_' || tenantid || '\".' || p_tablename || ' ( ' || array_to_string( 30 array(

31 select concat(c1, c2, c3, c4, c5, c6) as column_line 32 from (

33 select '\"' || column_name || '\"' || ' ' || 34 case

35 when data_type = 'ARRAY' then ltrim(udt_name, '_') || '[]' 36 when data_type = 'USER-DEFINED' then udt_name

37 else data_type end as c1,

38 case when character_maximum_length > 0 then '(' || character_maximum_length || ')' end as c2, 39 case when numeric_precision > 0 and numeric_scale < 1 then null end as c3, 40 case when numeric_precision > 0 and numeric_scale > 0 then null end as c4, 41 case when is_nullable = 'NO' then ' NOT NULL' end as c5, 42 case when column_default is not Null then ' DEFAULT' end || ' ' || replace(case

43 when position('nextval(''\"' in column_default) > 0

44 then replace(column_default, 'nextval(''\"', 'nextval(''\"' || 'sch_' || tenantid || '.') 45 when

46 position('nextval(''' in column_default) > 0 47 then

48 replace(column_default, 'nextval(''', 'nextval(''' || 'sch_' || tenantid || '.') 49 ELSE column_default

50 end, '::character varying', '') as c6 51 from information_schema.columns 52 where table_name = p_tablename

53 and table_catalog = dbinstancename and table_schema = p_namespace 55 order by ordinal_position 56 ) as string_columns 57 ), ' , ') || ','; 58 59

60 -- 约束

61 constraintIds := array(select oid

62 from pg_constraint c 63 where conrelid = ( select oid 65 from pg_class

66 where relname = p_tablename 67 and relnamespace = ( 68 select oid

69 from pg_namespace

70 where nspname = p_namespace 71 ) 72 )); 73

74 if array_length(constraintIds, 1) > 0 then 75 for i in 1..array_length(constraintIds, 1) 76 loop

77 v_conname := conname

78 from pg_constraint c

79 where c.oid = constraintIds[i];

80 constraintScripe := constraintScripe || ' CONSTRAINT '

81 '\"' || v_conname || '\"' || pg_get_constraintdef(constraintIds[i]) || ','; 82 end loop;

83 constraintScripe := rtrim(constraintScripe, ','); 84 end if; 85

86 if constraintScripe = '' or constraintScripe is null then 87 tableScript := trim(trailing ',' from tableScript); 88 end if;

tableScript := tableScript || constraintScripe || ' ); '; 90

91 /** **/

92 --- 获取⾮约束索引 column

93 indexScripe := array_to_string(ARRAY(SELECT CASE

94 WHEN position('\"' in indexdef) > 0 THEN

95 replace(indexdef,' INDEX ',' INDEX IF NOT EXISTS ') 96 ELSE

97 replace(replace(indexdef,' INDEX ',' INDEX IF NOT EXISTS '), indexname, '\"' || indexname || '\"') 98 END

99 from pg_indexes i

100 where i.schemaname = p_namespace101 and i.tablename = p_tablename102 and i.indexname not in

103 (select distinct conname104 from pg_constraint c105 where conrelid = (106 select oid107 from pg_class

108 where relname = p_tablename109 and relnamespace = (110 select oid

111 from pg_namespace

112 where nspname = p_namespace113 )114 ))), ';');115

116 if indexScripe <> '' or indexScripe is not null then

117 indexScripe := replace(indexScripe, ' ' || p_namespace || '.' || p_tablename || ' ', ' \"sch_' || tenantid || '\".' || p_tablename || ' ') || ';';118 tableScript := tableScript || indexScripe;119 end if;120 121

122 return tableScript;123 124 end

125 $BODY$

126 LANGUAGE plpgsql VOLATILE127 COST 100