PostgreSQL根据元数据批量生成建表语句

刚写完代码，直接贴上来，后续补注释。代码中很多变量可以提取出来用参数填充，代码量和可读性会更好一点，后续使用存储过程优化。

select 'create foreign table '
       || case when split_part(t.tablename, '_', length(replace(t.tablename, '_', '__')) - length(t.tablename) +1)='t'
               then regexp_replace( t.tablename,'_t\M','_ft','g') else t.tablename||'_ft' end
       ||CHR(10) || '('
       ||CHR(10) ||string_agg(t.col_name||' '||col_type,','||CHR(10))
       ||CHR(10)||')'
       ||CHR(10)|| 'server df_lims '
       ||CHR(10)|| 'options (schema_name ''lims'', table_name '''|| t.tablename ||''');'
       ||CHR(10)|| 'comment on foreign table ' || case when split_part(t.tablename, '_', length(replace(t.tablename, '_', '__')) - length(t.tablename) +1)='t'
                                                      then regexp_replace( t.tablename,'_t\M','_ft','g') else t.tablename||'_ft' end
       ||' is '''
       ||t.table_desc ||''';'
       ||CHR(10)||'alter foreign table '||case when split_part(t.tablename, '_', length(replace(t.tablename, '_', '__')) - length(t.tablename) +1)='t'
                                                      then regexp_replace( t.tablename,'_t\M','_ft','g') else t.tablename||'_ft' end
       ||' owner to df_edc_user;'
from (
               SELECT pc.relname                                                                                 AS tablename,
                      pd.description                                                                             AS table_desc,
                      pa.attnum                                                                                  AS col_sort_num,
                      pa.attname                                                                                 AS col_name,
                      concat_ws('', pt.typname,
                                SUBSTRING(format_type(pa.atttypid, pa.atttypmod) FROM '\(.*\)'))                 AS col_type,
                      pd2.description                                                                            AS col_desc
               FROM pg_class as pc
                        INNER JOIN pg_namespace as pn
                                   ON (pc.relnamespace = pn.oid AND pn.nspname = 'lims' AND pc.relkind = 'r')
                        INNER JOIN pg_description AS pd ON (pc.oid = pd.objoid AND pd.objsubid = 0)
                        INNER JOIN pg_attribute AS pa ON (pa.attrelid = pc.oid AND pa.attnum > 0)
                        INNER JOIN pg_type AS pt ON (pt.oid = pa.atttypid)
                        LEFT JOIN pg_description AS pd2 ON (pd2.objoid = pa.attrelid AND pd2.objsubid = pa.attnum)
               --where pc.relname='bas_dictionary_t'
               ORDER BY pc.relname, pa.attnum
               ) t
group by t.tablename,t.table_desc;

最终的交表语句：

字段的注释也可以批量生成

select 'comment on column '||t.tablename||'.'||t.col_name||' is '||'''报告是否带有解读'';'
from (
               SELECT pc.relname                                                                 AS tablename,
                      pd.description                                                             AS table_desc,
                      pa.attnum                                                                  AS col_sort_num,
                      pa.attname                                                                 AS col_name,
                      concat_ws('', pt.typname,
                                SUBSTRING(format_type(pa.atttypid, pa.atttypmod) FROM '\(.*\)')) AS col_type,
                      pd2.description                                                            AS col_desc
               FROM pg_class as pc
                        INNER JOIN pg_namespace as pn
                                   ON (pc.relnamespace = pn.oid AND pn.nspname = 'lims' AND pc.relkind = 'r')
                        INNER JOIN pg_description AS pd ON (pc.oid = pd.objoid AND pd.objsubid = 0)
                        INNER JOIN pg_attribute AS pa ON (pa.attrelid = pc.oid AND pa.attnum > 0)
                        INNER JOIN pg_type AS pt ON (pt.oid = pa.atttypid)
                        LEFT JOIN pg_description AS pd2 ON (pd2.objoid = pa.attrelid AND pd2.objsubid = pa.attnum)
                    --where pc.relname='bas_dictionary_t'
               ORDER BY pc.relname, pa.attnum
               ) t;