PostgreSQL中如何获取对象的DDL语句

版权声明：本文为Buddy Yuan原创文章，未经允许不得转载。原文地址： PostgreSQL中如何获取对象的DDL语句

我们在Oracle当中，可以使用dbms_metadata.get_ddl()方法去查看创建的语句。也可以使用expdp/impdp生成元数据dmp文件，在转换成sqlfile去查看。但是在大PG里面我们应该怎么做呢？

postgres=# create table t1(id int,name varchar(30));
CREATE TABLE
postgres=# insert into t1(id,name) values (generate_series(1,1000000),'test'||generate_series(1,1000000));
INSERT 0 1000000
postgres=# select count(*) from t1;
  count
---------
 1000000
(1 row)

postgres=# create unique index idx1 on t1(id);
CREATE INDEX

postgres=# create index idx2 on t1(name);
CREATE INDEX

postgres=# create view v1 as select id from t1;
CREATE VIE

postgres=# alter table t1 add constraint con1 check (id< 2000000);
ALTER TABLE

create function add(int,int) returns int
as
'select $1 + $2;'
language sql
immutable returns null on null input;

postgres=# select add(1,2);
 add
-----
   3
(1 row)

PostgreSQL附带了一组函数，具体可以查看https://www.postgresql.org/docs/current/static/functions-info.html，一些函数可以获得对象的定义。如获取视图的定义可以使用pg_get_viewde，获取触发器可以使用pg_get_triggerdef,获取函数可以使用pg_get_functiondef，获取约束可以使用pg_get_constraintdef。

postgres=# select pg_get_viewdef('v1');
 pg_get_viewdef
----------------
  SELECT t1.id +
    FROM t1;
(1 row)

postgres=# SELECT conname, pg_get_constraintdef(r.oid, true) as definition FROM pg_constraint r WHERE r.conrelid = 't1'::regclass;
 conname |      definition
---------+----------------------
 con1    | CHECK (id < 2000000)
(1 row)

postgres=# SELECT proname,pg_get_functiondef(a.oid) FROM pg_proc a WHERE a.proname = 'add';
 proname |                   pg_get_functiondef
---------+---------------------------------------------------------
 add     | CREATE OR REPLACE FUNCTION public.add(integer, integer)+
         |  RETURNS integer                                       +
         |  LANGUAGE sql                                          +
         |  IMMUTABLE STRICT                                      +
         | AS $function$select $1 + $2;$function$                 +
         |
(1 row)

postgres=# select pg_get_indexdef('idx1'::regclass);
                    pg_get_indexdef
--------------------------------------------------------
 CREATE UNIQUE INDEX idx1 ON public.t1 USING btree (id)
(1 row)

但是这些功能里面没有获取表定义的。唯一的办法是使用pg_dump。 使用pg_dump我们可以把表还有索引的语句都dump出来。这里使用-s选项(schema only)和-t选项(tables)。

-bash-4.2$ pg_dump -s -t t1 postgres | egrep -v "^--|^$"
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE public.t1 (
    id integer,
    name character varying(30),
    CONSTRAINT con1 CHECK ((id < 2000000))
);
ALTER TABLE public.t1 OWNER TO postgres;
CREATE UNIQUE INDEX idx1 ON public.t1 USING btree (id);
CREATE INDEX idx2 ON public.t1 USING btree (name);

当然还可以使用pl/perl语言的扩展来实现这件事。

postgres=# create extension plperlu;
CREATE EXTENSION

postgres=# \dx
                                List of installed extensions
    Name     | Version |   Schema   |                      Description
-------------+---------+------------+-------------------------------------------------------
 pageinspect | 1.6     | public     | inspect the contents of database pages at a low level
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 plperlu     | 1.0     | pg_catalog | PL/PerlU untrusted procedural language
(3 rows)

postgres=# CREATE OR REPLACE FUNCTION system(text) RETURNS text
AS 'my $cmd=shift; return `cd /tmp;$cmd`;' LANGUAGE plperlu;
CREATE FUNCTION

postgres=# select system('pg_dump -s -t t1 ddl | egrep -v "^--|^$"');
                    system
----------------------------------------------------------------
SET statement_timeout = 0;                                     +
SET lock_timeout = 0;                                          +
SET idle_in_transaction_session_timeout = 0;                   +
SET client_encoding = 'UTF8';                                  +
SET standard_conforming_strings = on;                          +
SELECT pg_catalog.set_config('search_path', '', false);        +
SET check_function_bodies = false;                             +
SET client_min_messages = warning;                             +
SET row_security = off;                                        +
SET default_tablespace = '';                                   +
SET default_with_oids = false;                                 +
CREATE TABLE public.t1 (                                       +
    id integer,                                                +
    name character varying(30),                                +
    CONSTRAINT con1 CHECK ((id < 2000000))                     +
);                                                             +
ALTER TABLE public.t1 OWNER TO postgres;                       +
CREATE UNIQUE INDEX idx1 ON public.t1 USING btree (id);        +
CREATE INDEX idx2 ON public.t1 USING btree (name);             +