1.前言
PostgreSQL中，要想看看表的定义或者索引的定义或者函数存储过程的定义，基本上已有的功能也还算够用。唯独表的定义获取不是那么直观，对吧？
我在自己实现的一套工具JMyETL以及XSQL当中，是直接使用JDBC提取元信息，然后按照标准的SQL语法生成建表语句。那种是通用的做法，也是一些第三方工具的标准做法，但是比较费人力。以后在机会合适的时候，我会将它们以某种形式开放出来，对于跨DBMS的数据移值还是非常有用的。
下边尝试直接使用PostgreSQL中的已有功能进行一些探索。
2.实例
2.1表的定义
目前，如果不做任何调整，我们可以通过元命令：\d+ 大致看到表的定义是下边这个样子的：
表原始建表语句:
1postgres=# create table t2(id int, col2 text) tablespace myts;
2CREATE TABLE
3postgres=# insert into t2 select n, md5(random()::text) || n from generate_series(1,10000) as n;
4INSERT 0 10000
1、元命令查看：
1postgres=# \d+ t2
2 Table "public.t2"
3 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
4--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
5 id | integer | | | | plain | | |
6 col2 | text | | | | extended | | |
7Tablespace: "myts"
8Access method: heap
如果只是想了解一下表的基本信息，它也已经足够 。可是要得到建表语句，这些是不够的。
2、命令行查看
我们可以借用pg_dump命令来看看，借用下边两个选项
1-s, --schema-only dump only the schema, no data
2-t, --table=PATTERN dump the specified table(s) only
实例：
1pg_dump -d postgres -s -t t2 | egrep -v "^--|^$|^SET"
2结果：
3SELECT pg_catalog.set_config('search_path', '', false);
4CREATE TABLE public.t2 (
5 id integer,
6 col2 text
7);
8ALTER TABLE public.t2 OWNER TO postgres;
9CREATE INDEX idx_t2_col2 ON public.t2 USING btree (col2);
或者直接在psql里头执行：
1postgres=# \! pg_dump -d postgres -s -t t2 | egrep -v "^--|^$|^SET"
2SELECT pg_catalog.set_config('search_path', '', false);
3CREATE TABLE public.t2 (
4 id integer,
5 col2 text
6);
7ALTER TABLE public.t2 OWNER TO postgres;
8CREATE INDEX idx_t2_col2 ON public.t2 USING btree (col2);
初看着，效果似乎还不错。
3、弄一个函数试试
我们再读读文档：https://www.postgresql.org/docs/current/functions-info.html, 也许自己可以试着写一个, stackoverflow上边有现成的，而https://github.com/filiprem/pg-tools 上边也有相关的代码片段。直接拿来看下：
1CREATE OR REPLACE FUNCTION tabledef(oid) RETURNS text
2LANGUAGE sql STRICT AS $$
3/* snatched from https://github.com/filiprem/pg-tools */
4WITH attrdef AS (
5 SELECT
6 n.nspname,
7 c.relname,
8 pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts,
9 c.relpersistence,
10 a.attnum,
11 a.attname,
12 pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype,
13 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d
14 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault,
15 a.attnotnull,
16 (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
17 WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) as attcollation,
18 a.attidentity,
19 a.attgenerated
20 FROM pg_catalog.pg_attribute a
21 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
22 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
23 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
24 WHERE a.attrelid = $1
25 AND a.attnum > 0
26 AND NOT a.attisdropped
27 ORDER BY a.attnum
28),
29coldef AS (
30 SELECT
31 attrdef.nspname,
32 attrdef.relname,
33 attrdef.relopts,
34 attrdef.relpersistence,
35 pg_catalog.format(
36 '%I %s%s%s%s%s',
37 attrdef.attname,
38 attrdef.atttype,
39 case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end,
40 case when attrdef.attnotnull then ' NOT NULL' else '' end,
41 case when attrdef.attdefault is null then ''
42 else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault)
43 when attrdef.attgenerated <> '' then ' GENERATED AS NOT_IMPLEMENTED'
44 else pg_catalog.format(' DEFAULT %s', attrdef.attdefault)
45 end
46 end,
47 case when attrdef.attidentity<>'' then pg_catalog.format(' GENERATED %s AS IDENTITY',
48 case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end)
49 else '' end
50 ) as col_create_sql
51 FROM attrdef
52 ORDER BY attrdef.attnum
53),
54tabdef AS (
55 SELECT
56 coldef.nspname,
57 coldef.relname,
58 coldef.relopts,
59 coldef.relpersistence,
60 string_agg(coldef.col_create_sql, E',\n ') as cols_create_sql
61 FROM coldef
62 GROUP BY
63 coldef.nspname, coldef.relname, coldef.relopts, coldef.relpersistence
64)
65SELECT
66 format(
67 'CREATE%s TABLE %I.%I%s%s%s;',
68 case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end,
69 tabdef.nspname,
70 tabdef.relname,
71 coalesce(
72 (SELECT format(E'\n PARTITION OF %I.%I %s\n', pn.nspname, pc.relname,
73 pg_get_expr(c.relpartbound, c.oid))
74 FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhrelid
75 JOIN pg_class pc ON pc.oid = i.inhparent
76 JOIN pg_namespace pn ON pn.oid = pc.relnamespace
77 WHERE c.oid = $1),
78 format(E' (\n %s\n)', tabdef.cols_create_sql)
79 ),
80 case when tabdef.relopts <> '' then format(' WITH (%s)', tabdef.relopts) else '' end,
81 coalesce(E'\nPARTITION BY '||pg_get_partkeydef($1), '')
82 ) as table_create_sql
83FROM tabdef
84$$;
调用实例：
1postgres=# SELECT tabledef('test1'::regclass);
2 tabledef
3-----------------------------
4 CREATE TABLE public.test1 (+
5 a integer +
6 );
7(1 row)
8
9postgres=# SELECT tabledef('t2'::regclass);
10 tabledef
11--------------------------
12 CREATE TABLE public.t2 (+
13 id integer, +
14 col2 text +
15 );
16(1 row)
看起来也不错的。只是里边如果有指定tablespace之类的，就信息不太全。但是它能适用于绝大多数情况。
当然，还有一个插件：pgddl，也能达到目的，不再赘述，可自行试用。
2.2索引的定义
假定索引定义为：
1postgres=# create index idx_t2_col2 on t2(col2);
2CREATE INDEX
则可以用元命令\di+， 查看它的基本信息：
1postgres=# \di+ idx_t2_col2
2 List of relations
3 Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
4--------+-------------+-------+----------+-------+-------------+---------------+--------+-------------
5 public | idx_t2_col2 | index | postgres | t2 | permanent | btree | 592 kB |
6(1 row)
要得到完整的索引创建的语句，可以使用PG中自带的系统函数(pg_get_indexdef)，输入参数是index对象的OID。
1postgres=# select oid, relname, relkind from pg_class where relname = 'idx_t2_col2';
2 oid | relname | relkind
3-------+-------------+---------
4 16401 | idx_t2_col2 | i
5(1 row)
6
7postgres=# select * from pg_get_indexdef(16401);
8 pg_get_indexdef
9----------------------------------------------------------
10 CREATE INDEX idx_t2_col2 ON public.t2 USING btree (col2)
11(1 row)
提示，因为我们知道对象名：idx_t2_col2, 它在pg_class中有记录，我们可以直接使用类型转换 'idx_t2_col2'::regclass
这样，可以一次得到结果：
1postgres=# select * from pg_get_indexdef('idx_t2_col2'::regclass);
2 pg_get_indexdef
3----------------------------------------------------------
4 CREATE INDEX idx_t2_col2 ON public.t2 USING btree (col2)
5(1 row)
那么要得到一张表't2'所有的索引名呢？查询视图：pg_indexes。这个也很有用，对不对？
1postgres=# select * from pg_indexes where tablename = 't2';
2 schemaname | tablename | indexname | tablespace | indexdef
3------------+-----------+-------------+------------+----------------------------------------------------------
4 public | t2 | idx_t2_col2 | | CREATE INDEX idx_t2_col2 ON public.t2 USING btree (col2)
5(1 row)
pg_indexes视图的原始定义是：
1SELECT
2 n.nspname AS schemaname,
3 c.relname AS tablename,
4 i.relname AS indexname,
5 t.spcname AS tablespace,
6 pg_get_indexdef(i.oid) AS indexdef
7FROM pg_index x
8 JOIN pg_class c ON c.oid = x.indrelid
9 JOIN pg_class i ON i.oid = x.indexrelid
10 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
11 LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
12WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND i.relkind = 'i'::"char";
2.3函数或存储过程的定义
我们就以下这典型的函数为例：
1CREATE TABLE test1(a int);
2
3CREATE PROCEDURE transaction_test1()
4LANGUAGE plpgsql
5AS $$
6BEGIN
7 FOR i IN 0..9 LOOP
8 INSERT INTO test1 (a) VALUES (i);
9 IF i % 2 = 0 THEN
10 COMMIT;
11 ELSE
12 ROLLBACK;
13 END IF;
14 END LOOP;
15END;
16$$;
17
18CALL transaction_test1();
19
20CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
21 SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', round(random() * 52 + 0.5)::integer, 1), '')
22 FROM generate_series(1, $1);
23$$ language sql;
1、元命令获取信息：
1postgres=# \df+ random_string
2
3 List of functions
4 Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privilege
5s | Language | Source code
6 | Description
7--------+---------------+------------------+---------------------+------+------------+----------+----------+----------+-----------------
8--+----------+--------------------------------------------------------------------------------------------------------------------------
9------------+-------------
10 public | random_string | text | integer | func | volatile | unsafe | postgres | invoker |
11 | sql |
12 +|
13 | | | | | | | | |
14 | | SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', round(random() * 52 + 0.5)::integ
15er, 1), '')+|
16 | | | | | | | | |
17 | | FROM generate_series(1, $1);
18 +|
19 | | | | | | | | |
20 | |
21 |
22(1 row)
2、使用系统函数
直接使用函数：pg_get_functiondef(oid)
1postgres=# select * from pg_get_functiondef('transaction_test1'::regproc);
2 pg_get_functiondef
3--------------------------------------------------------
4 CREATE OR REPLACE PROCEDURE public.transaction_test1()+
5 LANGUAGE plpgsql +
6 AS $procedure$ +
7 BEGIN +
8 FOR i IN 0..9 LOOP +
9 INSERT INTO test1 (a) VALUES (i); +
10 IF i % 2 = 0 THEN +
11 COMMIT; +
12 ELSE +
13 ROLLBACK; +
14 END IF; +
15 END LOOP; +
16 END; +
17 $procedure$ +
18
19(1 row)
20
21postgres=# select * from pg_get_functiondef('random_string'::regproc);
22 pg_get_functiondef
23--------------------------------------------------------------------------------------------------------------------------------------
24 CREATE OR REPLACE FUNCTION public.random_string(integer)
25 RETURNS text
26 LANGUAGE sql
27 AS $function$
28 SELECT string_agg(substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', round(random() * 52 + 0.5)::integer, 1), '')+
29 FROM generate_series(1, $1);
30 $function$
31(1 row)
2.4视图的定义
1postgres=# \df pg_get_viewdef
2 List of functions
3 Schema | Name | Result data type | Argument data types | Type
4------------+----------------+------------------+---------------------+------
5 pg_catalog | pg_get_viewdef | text | oid | func
6 pg_catalog | pg_get_viewdef | text | oid, boolean | func
7 pg_catalog | pg_get_viewdef | text | oid, integer | func
8 pg_catalog | pg_get_viewdef | text | text | func
9 pg_catalog | pg_get_viewdef | text | text, boolean | func
10(5 rows)
11
12postgres=# create view t2_v as select * from t2;
13CREATE VIEW
1、元命令方式：
1postgres=# \dv+ t2_v
2 List of relations
3 Schema | Name | Type | Owner | Persistence | Size | Description
4--------+------+------+----------+-------------+---------+-------------
5 public | t2_v | view | postgres | permanent | 0 bytes |
6(1 row)
2、查询视图方式：
1postgres=# select * from pg_views where viewname = 't2_v';
2 schemaname | viewname | viewowner | definition
3------------+----------+-----------+----------------
4 public | t2_v | postgres | SELECT t2.id,+
5 | | | t2.col2 +
6 | | | FROM t2;
7(1 row)
3、系统函数方式:
1postgres=# select * from pg_get_viewdef('t2_v'::regclass);
2 pg_get_viewdef
3----------------
4 SELECT t2.id,+
5 t2.col2 +
6 FROM t2;
7(1 row)
3.总结
有点闹不明白，PostgreSQL这是闹哪一出，它可以有index, proc, view ddl完整的支持，可是一个普普通通的Table的DDL语句功能，竟然没有。一定要通过pg_dump之类的原始命令。或者，设计者以为直接一个元命令\d, \d+之类的，能看到各列的元信息就够了。但这个对于某些层面的应用开发而言，却是非常不方便的。
要实现这个功能，在现在的PG代码的基础上去搞，应该基本上不费什么劲，毕竟pg_dump里头全都能给你弄出来。
先总结这么多，理想的情况是，有一个函数：pg_get_table_ddl('abcde'::regclass), 然后它会把相关的index, view以及依赖的proc/fun DDL全部给你显示出来。
后边有时间再总结一下，弄出一个PostgreSQL中DDL的获取方法(二)
4.参考
[1] https://stackoverflow.com/questions/1846542/postgresql-get-table-definition-pg-get-tabledef
[2] https://github.com/lacanoid/pgddl
[3] https://www.postgresql.org/docs/current/functions-info.html
文章转载自数据库杂记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
