PostgreSQL 元数据库讲解 - 对象(表、索引、函数、序列、视图...)在哪里、如何识别、如何求对象定义(DDL语句) - show create

digoal 2017-12-08

1598

作者

digoal

日期

2017-12-08

背景

PostgreSQL中，所有对象的定义都在元数据库中，详见

https://www.postgresql.org/docs/10/static/catalogs.html

除了元数据库，还有一些管理函数，如下

https://www.postgresql.org/docs/10/static/functions-admin.html

下面简单介绍一下元数据库的使用。

如何识别对象

1、识别临时表、UNLOGGED TABLE、临时表

select relname from pg_class where relpersistence=? and relkind='r';

pg_class 的relpersistence用于识别表是什么表(正常表、不记日志表、临时表)。 relkind用于识别是什么对象类别(表、索引、序列、切片、视图、物化视图、复合类型、外部表、分区表)。

relpersistence p = permanent table, u = unlogged table, t = temporary table relkind r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table

Greenplum 扩展

pg_class.relstorage 用于区分是什么存储

h = 堆表(heap) a = append only row存储表 c = append only column存储表

存储过程

pg_proc

数据库

pg_database

表空间

pg_tablespace

schema

pg_namespace

用户

pg_roles

索引接口

pg_am

如何获取对象定义

使用这些函数接口，可以获得对应对象的定义。

```
pg_get_indexdef

pg_get_functiondef

pg_get_triggerdef

pg_get_ruledef

pg_get_viewdef

pg_get_constraintdef
```

例子

```
postgres=# select * from pg_get_indexdef('idx_tbl2_1'::regclass);
pg_get_indexdef

CREATE INDEX idx_tbl2_1 ON tbl2 USING btree (id)
(1 row)
```

如何获得建表的DDL

没有show create的语法，pg_dump的结构导出是如何做到的？pg_admin GUI工具又是如何生成DDL的？

方法1：

从pg_admin工具的源码中，可以获知如何生成建表（包括其他对象）的DDL。

方法2：

从pg_dump的源码中，可以获知如何生成建表（包括其他对象）的DDL。

方法3：

从 Information Schema 中获得对象定义，使用依赖关系系统表，获得相关对象，并获得相关对象的定义。

《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》

https://stackoverflow.com/questions/25639088/show-table-structure-and-list-of-tables-in-postgresql

http://www.postgresqltutorial.com/postgresql-describe-table/

https://www.postgresql.org/docs/current/information-schema.html

https://dba.stackexchange.com/questions/48451/how-to-get-table-definition-of-a-postgresql-table

postgres=# create or replace function describe_table(tbl_name text) returns table(column_name varchar, data_type varchar,character_maximum_length int, numeric_precision int, numeric_precision_radix int, numeric_scale int) as $$ select column_name, data_type, character_maximum_length, numeric_precision,numeric_precision_radix,numeric_scale from INFORMATION_SCHEMA.COLUMNS where table_name = $1; $$ language 'sql'; CREATE FUNCTION postgres=# select * from describe_table('test'); column_name | data_type | character_maximum_length | numeric_precision | numeric_precision_radix | numeric_scale -------------+-----------------------------+--------------------------+-------------------+-------------------------+--------------- id | integer | | 32 | 2 | 0 c1 | bigint | | 64 | 2 | 0 c2 | smallint | | 16 | 2 | 0 c3 | double precision | | 53 | 2 | c4 | real | | 24 | 2 | c5 | numeric | | | 10 | c6 | numeric | | 10 | 10 | 2 c7 | timestamp without time zone | | | | c8 | character varying | 100 | | | (9 rows)

方法4：

使用psql客户端得到获取对象定义的SQL，并从中获取DDL

```
digoal@pg11-test-> psql -E
psql (11.1)
Type "help" for help.

postgres=# \d+ test
* QUERY *****
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
*****

* QUERY *****
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
, c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '1912240';
*****

* QUERY *****
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attstorage,
CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,
pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '1912240' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
*****

* QUERY *****
SELECT inhparent::pg_catalog.regclass,
pg_catalog.pg_get_expr(c.relpartbound, inhrelid),
pg_catalog.pg_get_partition_constraintdef(inhrelid)
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid = inhrelid
WHERE c.oid = '1912240' AND c.relispartition;
*****

* QUERY *****
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '1912240' ORDER BY 1;
*****

* QUERY *****
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
FROM pg_catalog.unnest(stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND
a.attnum = s.attnum AND NOT attisdropped)) AS columns,
'd' = any(stxkind) AS ndist_enabled,
'f' = any(stxkind) AS deps_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '1912240'
ORDER BY 1;
*****

* QUERY *****
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
WHERE pr.prrelid = '1912240'
UNION ALL
SELECT pubname
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('1912240')
ORDER BY 1;
*****

* QUERY *****
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '1912240' AND c.relkind != 'p' ORDER BY inhseqno;
*****

* QUERY *****
SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '1912240' ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
*****

                                           Table "public.test"

参考