PostgreSQL 小工具——将一个数据库中所有表的设计整理为csv文件

工作中，需要将在 PostgreSQL 数据库中的所有表的结构整理成表格。示例如下：

person	人员表
字段名	字段类型	是否非空	约束	字段描述
id	int	Y	primary key (id)	主键，id
name	int	Y		姓名
......

 

笔者编写了一个 PostgreSQL 脚本，它可以将一个数据库中的所有表的结构整理到一个 csv 文件中。下面是它的代码：

set client_encoding = 'UTF8';

drop function if exists func_get_constraint_typename(char(1)) ;
drop function if exists func_getdef(text);

create or replace function func_get_constraint_typename(typecode char(1)) returns text as
$$
begin
    return
    case typecode
        when 'c' then 'check'
        when 'f' then 'foreign key'
        when 'p' then 'primary key'
        when 'u' then 'unique'
        when 'x' then 'exclude'
        else null end;
end;
$$language plpgsql;

create or replace function func_getdef( my_tbname text default 'all') returns integer as
$$
declare v_red record;
declare v_tbnm text;
declare v_data_dir text;
declare v_file_name text;

begin
    select setting   into  v_data_dir from pg_settings where name  = 'data_directory';

    drop table if exists tb_resule_csv;
    create  unlogged table  tb_resule_csv (autoid serial,column_name text, col_type text, is_notnull text ,col_contraint text, col_comment text);

    v_tbnm='';
    if my_tbname = 'all' then
        for v_red in
        select  a.attrelid::regclass::text as tb_name,
        obj_description(a.attrelid, 'pg_class') as tb_comment,
        count(a.attnum) over(partition by a.attrelid::regclass::text) as col_number,
        a.attname as col_name,
        replace(format_type(a.atttypid,a.atttypmod),'character varying','varchar') as  col_type,
        case a.attnotnull when 't' then 'Y' else '' end as is_notnull,
        func_get_constraint_typename(c.contype::char) || ' (' ||(select string_agg(attname,', ' order by rn) from pg_attribute inner join (select row_number() over() rn,conkey,conrelid from (select unnest(c.conkey) as conkey) a) b on (pg_attribute.attnum = b.conkey and pg_attribute.attrelid = b.conrelid)) || ')' as col_constraint,
        col_description(a.attrelid, a.attnum) as col_comment
        from  pg_attribute  a
        inner join  (select tablename::regclass::oid as tboid   from pg_tables where schemaname  in ('public'))b on (a.attrelid=b.tboid)
        left  join  pg_constraint c on (a.attrelid=c.conrelid and a.attnum in (select unnest(c.conkey)))
        where a.attnum >0 and attisdropped = 'false'::bool and  a.attrelid::regclass::text <> 'tb_resule_csv' and a.attrelid not in (select inhrelid from pg_inherits) order by tb_name, attnum loop

            if v_tbnm <> v_red.tb_name then
                insert into  tb_resule_csv (column_name, col_type, is_notnull, col_contraint, col_comment) values(null,null,null,null,null),(v_red.tb_name,v_red.tb_comment,null,null,null),('字段名','字段类型','是否非空','约束','字段描述');
                insert into  tb_resule_csv (column_name, col_type, is_notnull, col_contraint, col_comment) values(v_red.col_name,v_red.col_type,v_red.is_notnull,v_red.col_constraint,v_red.col_comment);
                v_tbnm=v_red.tb_name;
            else
                insert into  tb_resule_csv (column_name, col_type, is_notnull, col_contraint, col_comment) values(v_red.col_name,v_red.col_type,v_red.is_notnull,v_red.col_constraint,v_red.col_comment);
            end if;

        end loop;
        v_file_name = v_data_dir || '/' || current_database()::text || '.csv';
        execute  'copy (select column_name, col_type, is_notnull ,col_contraint, col_comment from tb_resule_csv order by autoid)  to ' || '''' || v_file_name || '''' || ' with csv   encoding ' || '''' || 'GBK' || '''' ;


    else

        for v_red in  select  a.attrelid::regclass::text as tb_name,
        obj_description(a.attrelid, 'pg_class') as tb_comment,
        count(a.attnum) over(partition by a.attrelid::regclass::text) as col_number,
        a.attname as col_name,
        replace(format_type(a.atttypid,a.atttypmod),'character varying','varchar') as  col_type,
        case a.attnotnull when 't' then 'Y' else '' end as is_notnull,
        func_get_constraint_typename(c.contype::char) || ' (' ||(select string_agg(attname,', ' order by rn) from pg_attribute inner join (select row_number() over() rn,conkey,conrelid from (select unnest(c.conkey) as conkey) a) b on (pg_attribute.attnum = b.conkey and pg_attribute.attrelid = b.conrelid)) || ')' as col_constraint,
        col_description(a.attrelid, a.attnum) as col_comment
        from  pg_attribute  a
        inner join  (select tablename::regclass::oid as tboid   from pg_tables where schemaname in ('public'))b on (a.attrelid=b.tboid)
        left  join  pg_constraint c on (a.attrelid=c.conrelid and a.attnum in (select unnest(c.conkey)))
        where a.attnum >0 and  a.attrelid::regclass::text = my_tbname and attisdropped = 'false'::bool and  a.attrelid::regclass::text <> 'tb_resule_csv' and a.attrelid not in (select inhrelid from pg_inherits) order by tb_name, attnum loop

            if v_tbnm <> v_red.tb_name then
                insert into  tb_resule_csv (column_name, col_type, is_notnull, col_contraint, col_comment) values(null,null,null,null,null),(v_red.tb_name,v_red.tb_comment,null,null,null),('字段名','字段类型','是否非空','约束','字段描述');
                insert into  tb_resule_csv (column_name, col_type, is_notnull, col_contraint, col_comment) values(v_red.col_name,v_red.col_type,v_red.is_notnull,v_red.col_constraint,v_red.col_comment);
                v_tbnm=v_red.tb_name;
            else
                insert into  tb_resule_csv (column_name, col_type, is_notnull, col_contraint, col_comment) values(v_red.col_name,v_red.col_type,v_red.is_notnull,v_red.col_constraint,v_red.col_comment);
            end if;

        end loop;
        v_file_name = v_data_dir || '/' || current_database() || '_' || my_tbname || '.csv';
        execute  'copy (select column_name, col_type, is_notnull ,col_contraint, col_comment from tb_resule_csv order by autoid)  to ' || '''' || v_file_name || '''' || ' with csv   encoding ' || '''' || 'GBK' || '''' ;

    end if;


    drop table if exists tb_resule_csv cascade;

    return 0;

end;
$$ language plpgsql;

select func_getdef();

drop function if exists func_getdef(text);
drop function if exists func_get_constraint_typename(text);

 

使用方法很简单：你只需要在相应的数据库中执行这个脚本就可以了。

执行后，它会在数据库的data 目录中生成一个 csv 文件，与那个数据库同名。