获取表的ddl

来源：这里教程网时间：2026-03-14 20:15:13 作者：

作者：瀚高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 fGROUP 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)