作者
digoal
日期
2019-06-10
标签
PostgreSQL , ddlx , 对象ddl
背景
如何生成PG 创建对象的DDL语句，PG内置了一些函数，如下：
postgres=# \df *.*get*def*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------------------------+------------------+-----------------------+------
pg_catalog | pg_get_constraintdef | text | oid | func
pg_catalog | pg_get_constraintdef | text | oid, boolean | func
pg_catalog | pg_get_function_arg_default | text | oid, integer | func
pg_catalog | pg_get_functiondef | text | oid | func
pg_catalog | pg_get_indexdef | text | oid | func
pg_catalog | pg_get_indexdef | text | oid, integer, boolean | func
pg_catalog | pg_get_partition_constraintdef | text | oid | func
pg_catalog | pg_get_partkeydef | text | oid | func
pg_catalog | pg_get_ruledef | text | oid | func
pg_catalog | pg_get_ruledef | text | oid, boolean | func
pg_catalog | pg_get_statisticsobjdef | text | oid | func
pg_catalog | pg_get_triggerdef | text | oid | func
pg_catalog | pg_get_triggerdef | text | oid, boolean | func
pg_catalog | pg_get_viewdef | text | oid | func
pg_catalog | pg_get_viewdef | text | oid, boolean | func
pg_catalog | pg_get_viewdef | text | oid, integer | func
pg_catalog | pg_get_viewdef | text | text | func
pg_catalog | pg_get_viewdef | text | text, boolean | func
以上函数，可以获取的对象类型有限。只覆盖了视图、规则、触发器、函数、索引、约束、自定义统计信息等。
而表、表空间、序列、注释等定义没有办法获取，或者非常麻烦。
《PostgreSQL 元数据库讲解 - 对象(表、索引、函数、序列、视图...)在哪里、如何识别、如何求对象定义(DDL语句) - show create》
《PostgreSQL 使用函数生成 外部表DDL(use function generate foreign table DDL in postgresql)》
pg_dump, pg_dumpall等导出定义的客户端程序虽然可以获得对象定义，但是比较麻烦，有没有SQL接口直接获取定义呢？
现在有了ddlx插件，这些变得更简单了。
使用ddlx插件生成对象创建DDL
```
export PGHOME=/usr/pgsql-11
export PATH=$PGHOME/bin:$PATH:.
wget http://api.pgxn.org/dist/ddlx/0.11.0/ddlx-0.11.0.zip
unzip ddlx-0.11.0.zip
cd ddlx-0.11.0/
USE_PGXS=1 make
USE_PGXS=1 make install
```
```
root@pg11-test-> psql
psql (11.1)
Type "help" for help.
postgres=# create extension ddlx ;
CREATE EXTENSION
```
例子
```
postgres=# create table t_1(id int , info text, crt_time timestamp);
CREATE TABLE
postgres=# create index idx_t_1_1 on t_1(info);
CREATE INDEX
postgres=# create table t_2(like t_1);
CREATE TABLE
postgres=# create rule r1 as on insert TO t_1 do also insert into t_2 values (new.*);
CREATE RULE
postgres=# alter table t_1 add constraint pk_1 primary key (id);
ALTER TABLE
postgres=# alter table t_1 add column c1 oid;
ALTER TABLE
postgres=# select ddlx_create('t_1'::regclass);
ddlx_create
-- Type: TABLE ; Name: t_1; Owner: postgres +
+
CREATE TABLE t_1 ( +
id integer NOT NULL, +
info text, +
crt_time timestamp without time zone, +
c1 oid +
); +
+
COMMENT ON TABLE t_1 IS NULL; +
+
ALTER TABLE t_1 ADD CONSTRAINT pk_1 +
PRIMARY KEY (id); +
+
CREATE INDEX idx_t_1_1 ON public.t_1 USING btree (info); +
+
CREATE RULE r1 AS +
ON INSERT TO t_1 DO INSERT INTO t_2 (id, info, crt_time)+
VALUES (new.id, new.info, new.crt_time); +
+
ALTER TABLE t_1 OWNER TO postgres; +
(1 row)
```
DDLX提供的函数接口
```
postgres=# select proname from pg_proc where proname ~ '^ddlx';
proname
ddlx_alter_owner
ddlx_alter_table_defaults
ddlx_alter_table_storage
ddlx_banner
ddlx_comment
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create
ddlx_create_access_method
ddlx_create_aggregate
ddlx_create_cast
ddlx_create_class
ddlx_create_collation
ddlx_create_constraint
ddlx_create_constraints
ddlx_create_conversion
ddlx_create_database
ddlx_create_default
ddlx_create_event_trigger
ddlx_create_foreign_data_wrapper
ddlx_create_function
ddlx_create_index
ddlx_create_indexes
ddlx_create_language
ddlx_create_operator_family
ddlx_create_rule
ddlx_create_rules
ddlx_create_sequence
ddlx_create_server
ddlx_create_table
ddlx_create_tablespace
ddlx_create_text_search_parser
ddlx_create_text_search_template
ddlx_create_transform
ddlx_create_trigger
ddlx_create_triggers
ddlx_create_type_base
ddlx_create_type_domain
ddlx_create_type_enum
ddlx_create_type_range
ddlx_create_user_mapping
ddlx_create_view
ddlx_describe
ddlx_drop
ddlx_drop_constraint
ddlx_drop_default
ddlx_drop_trigger
ddlx_get_constraints
ddlx_get_dependants
ddlx_get_functions
ddlx_get_indexes
ddlx_get_rules
ddlx_get_triggers
ddlx_grants
ddlx_grants
ddlx_grants
ddlx_grants
ddlx_grants_columns
ddlx_identify
ddlx_script
ddlx_script
ddlx_script_parts
(70 rows)
```
参考
https://pgxn.org/dist/ddlx/0.11.0/
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
