版权声明:本文为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); +
Post a Comment