获取表的ddl

作者：瀚高PG实验室（Highgo PG Lab）-- 张波
PostgreSQL中没有像oracle一样获取表ddl的函数，下面提供两种方式获取表的ddl语句。功能比较简单仅实现普通表的ddl，如需其他类型的表或对象，还需要进行修改。
直接使用SQL查询，语句如下

with t as (
select schema_name,table_name,string_agg(column_name||' '||column_type||' '||column_default_value ||' '||column_not_null||chr(10),',') as aaa from(
SELECT 
      b.nspname as schema_name,
      b.relname as table_name,
      a.attname as column_name,
      pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
      CASE WHEN 
          (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
           FROM pg_catalog.pg_attrdef d
           WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
          'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                        FROM pg_catalog.pg_attrdef d
                        WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
      ELSE
          ''
      END as column_default_value,
      CASE WHEN a.attnotnull = true THEN 
          'NOT NULL'
      ELSE
          'NULL'
      END as column_not_null,
      a.attnum as attnum,
      e.max_attnum as max_attnum
  FROM 
      pg_catalog.pg_attribute a
      INNER JOIN 
       (SELECT c.oid,
          n.nspname,
          c.relname
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ ('^('||'修改为要获取的表名'||')$')
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 2, 3) b
      ON a.attrelid = b.oid
      INNER JOIN 
       (SELECT 
            a.attrelid,
            max(a.attnum) as max_attnum
        FROM pg_catalog.pg_attribute a
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        GROUP BY a.attrelid) e
      ON a.attrelid=e.attrelid
  WHERE a.attnum > 0 
    AND NOT a.attisdropped
  ORDER BY a.attnum) as f
GROUP by schema_name,table_name)
select 'create table '||schema_name||'.'||table_name||' ('||aaa||')' from t;

实现效果如下：

                       ?column?                        
-------------------------------------------------------
 create table public.emp (empno numeric(4,0)  NOT NULL+
 ,ename character varying  NULL                       +
 ,job character varying  NULL                         +
 ,mgr numeric(4,0)  NULL                              +
 ,hiredate date  NULL                                 +
 ,sal numeric(7,2)  NULL                              +
 ,comm numeric(7,2)  NULL                             +
 ,deptno numeric(2,0)  NULL                           +
 )
(1 row)

改写为存储过程，代码如下

create or replace function get_tab_ddl(tab_name varchar)
returns text as 
$$
declare 
    --定义变量
    tab_ddl text;
    curs refcursor;
    tmp_col record;
    tab_info record;
begin  
    --获取表的pid、schema信息
    open curs for SELECT c.oid,n.nspname,c.relname FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname ~ ('^('||tab_name||')$')AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2,3;
    fetch curs into tmp_col;
    --判断是否存在该表
    if tmp_col.oid is null then
        return 'Table "'||tab_name||'" was not queried';
    end if;
    --如表存在，获取表的列信息
    FOR tab_info IN 
        SELECT 
            a.attname as col_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as col_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as col_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as col_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND a.attrelid=tmp_col.oid
          AND NOT a.attisdropped
        ORDER BY a.attnum
    --拼接为ddl语句
    LOOP
        IF tab_info.attnum = 1 THEN
            tab_ddl:='CREATE TABLE '||tmp_col.nspname||'.'||tmp_col.relname||' (';
        ELSE
            tab_ddl:=tab_ddl||',';
        END IF;

        IF tab_info.attnum <= tab_info.max_attnum THEN
            tab_ddl:=tab_ddl||chr(10)||'    '||tab_info.col_name||' '||tab_info.col_type||' '||tab_info.col_default_value||' '||tab_info.col_not_null;
        END IF;
    END LOOP;
       tab_ddl:=tab_ddl||');';
    --输出结果
    RETURN tab_ddl;
end;
$$ language plpgsql;

实现效果如下

highgo=# select get_tab_ddl('emp');
            get_tab_ddl             
------------------------------------
 CREATE TABLE public.emp (         +
     empno numeric(4,0)  NOT NULL, +
     ename character varying  NULL,+
     job character varying  NULL,  +
     mgr numeric(4,0)  NULL,       +
     hiredate date  NULL,          +
     sal numeric(7,2)  NULL,       +
     comm numeric(7,2)  NULL,      +
     deptno numeric(2,0)  NULL);
(1 row)