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$;

免责声明:

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源,请您支持作者,购买正版内容。

6、资源失效,请下方留言,欢迎分享资源链接

文章评论

0条评论