postgresql中生成建表语句

postgresql中生成建表语句

主要思路

1）查询pg_attribute表，拼接字段信息（字段名，字段类型，是否非空，是否有默认值）

2）判断是否是分区表，是分区表调用分区函数，生成分区表创建语句，存在子分区时一查到底（查询完当前分区下所有子分区信息再查询下一个分区信息）

3）拼接表空间信息

效果展示

分区表test_p详情

生成的建表语句

格式化后的建表语句

CREATE TABLE test_p ( id integer not null ,num integer  ) partition by RANGE (id);
create table test_p11 partition of test_p FOR VALUES FROM (0) TO (5) TABLESPACE test_tsp;
create table test_p2 partition of test_p FOR VALUES FROM (10) TO (20);
create table test_p3 partition of test_p FOR VALUES FROM (20) TO (30) partition by RANGE (id) TABLESPACE test_tsp;
create table test_sp31 partition of test_p3 FOR VALUES FROM (25) TO (30) partition by RANGE (id);
create table test_ssp31 partition of test_sp31 FOR VALUES FROM (25) TO (28);
create table test_sp32 partition of test_p3 FOR VALUES FROM (20) TO (25);
create table test_p4 partition of test_p FOR VALUES FROM (30) TO (40);
create table test_p50 partition of test_p FOR VALUES FROM (5) TO (10);

函数详情

生成建表语句函数

CREATE OR REPLACE FUNCTION get_table_def(tablename regclass) RETURNS text LANGUAGE PLPGSQL AS $body$
DECLARE
  sub_res varchar := ' ';
  is_part int;
  result text;

BEGIN
  --table def
  result := 'CREATE TABLE ' || TABLENAME || ' ( ';
  select string_agg(a.attname||' '||format_type(a.atttypid, a.atttypmod)||' '||case when a.attnotnull then 'not null' else '' end||' '||case when a.atthasdef then 'default '||(select pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) from pg_attrdef d where d.adrelid=a.attrelid) else '' end,',')  into STRICT sub_res
  from pg_attribute a
  where a.attrelid=tablename::regclass and a.attnum>0 and a.attisdropped='f';
  result := result || sub_res || ')';
  --partition def
  select count(*) into is_part from pg_partitioned_table where partrelid=tablename;  
  if is_part > 0 then
    select pg_get_partkeydef(tablename::regclass) into sub_res;
	result := result || ' partition by ' ||  sub_res;
	--tablespace
    select b INTO sub_res from get_tablespace(tablename) b;
    result := result || sub_res || ';';
	select string_agg(create_sql,'') from get_partition_detail(tablename) into sub_res;
	result := result || sub_res;
  else
    --tablespace
    select b INTO sub_res from get_tablespace(tablename) b;
    result := result || sub_res ;
    result := result || ';';
  end if;
  RETURN result;  
END;
$body$;

分区表创建函数

CREATE OR REPLACE FUNCTION public.get_partition_detail(table_id regclass)
 RETURNS  table(parentrelid regclass,relid regclass,create_sql text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    relid regclass;
    parentrelid regclass;
    t_result text;
	sub_res varchar := ' ';
    type varchar;
    sql varchar;
	max_id int;
BEGIN
   --create temporary table
   create temporary table if not exists temp_partition_detail (id serial,parentrelid regclass,relid regclass,create_sql text,relkind varchar);
   execute 'select coalesce(max(id),0) from temp_partition_detail' into max_id;
   --get create_sql of next level partition table
   for relid,parentrelid,t_result,type in SELECT i.inhrelid,i.inhparent,concat_ws(' ','create table',inhrelid::regclass,'partition of',inhparent::regclass,pg_get_expr(c.relpartbound,c.oid)) as t_result,c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = table_id::regclass order by t_result
   loop
     if type = 'r' then
	   --tablespace
	   select b INTO sub_res from get_tablespace(relid) b;
	   t_result := t_result || sub_res || ';';
       insert into temp_partition_detail (parentrelid,relid,create_sql,relkind) values (parentrelid,relid,t_result,type);
	 elsif type = 'p' then
	   select pg_get_partkeydef(relid) into sub_res;
	   t_result := t_result || ' partition by ' || sub_res;
	   --tablespace
	   select b INTO sub_res from get_tablespace(relid) b;
	   t_result := t_result || sub_res || ';';
	   insert into temp_partition_detail (parentrelid,relid,create_sql,relkind) values (parentrelid,relid,t_result,type);
	   --call myself to get create_sql of next level partition table
	   sql := 'select * from get_partition_detail('''||relid||''')';
	   execute sql;
	 end if;

   end loop;
   return query select d.parentrelid,d.relid,d.create_sql from temp_partition_detail d where id>max_id order by d.id;
end;
$function$;

表空间信息函数

CREATE OR REPLACE FUNCTION get_tablespace(tablename regclass) RETURNS varchar LANGUAGE PLPGSQL AS $body$
DECLARE
  result varchar;
begin
--tablespace
       select b.spcname INTO result from pg_class a left join pg_tablespace b on a.reltablespace=b.oid where a.oid=tablename;
       IF result is not null THEN
  	     result := ' TABLESPACE ' || result;
	   else
	     result := '';
       END IF;
return result;
end;
$body$;