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 xxx.t1 postgres | egrep -v "^--|^$" xxx是schema
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); +