Skip to content

Files

Latest commit

Dec 15, 2023
f400df2 · Dec 15, 2023

History

History
297 lines (185 loc) · 10.6 KB

20190610_01.md

File metadata and controls

297 lines (185 loc) · 10.6 KB

PostgreSQL 生成对象DDL语句 - ddlx 插件 - 类似"show create"

作者

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/

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

digoal's wechat