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); +

















09-02 17:04
查看更多