ddlx
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_ddlx | 0.30 | ADMIN | PostgreSQL | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 5080 | ddlx | No | Yes | No | Yes | No | No | - |
| Related | pgdd pg_checksums pg_permissions pgextwlist pg_catcheck adminpack pg_repack pg_rewrite |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 0.30 | 1817161514 | pg_ddlx | - |
| RPM | PIGSTY | 0.30 | 1817161514 | ddlx_$v | - |
| DEB | PIGSTY | 0.30 | 1817161514 | postgresql-$v-ddlx | - |
Build
You can build the DEB packages for pg_ddlx using pig build:
pig build pkg pg_ddlx # build DEB packages
Install
You can install pg_ddlx directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_ddlx; # Install for current active PG version
pig ext install -y pg_ddlx -v 18 # PG 18
pig ext install -y pg_ddlx -v 17 # PG 17
pig ext install -y pg_ddlx -v 16 # PG 16
pig ext install -y pg_ddlx -v 15 # PG 15
pig ext install -y pg_ddlx -v 14 # PG 14
dnf install -y ddlx_18 # PG 18
dnf install -y ddlx_17 # PG 17
dnf install -y ddlx_16 # PG 16
dnf install -y ddlx_15 # PG 15
dnf install -y ddlx_14 # PG 14
apt install -y postgresql-18-ddlx # PG 18
apt install -y postgresql-17-ddlx # PG 17
apt install -y postgresql-16-ddlx # PG 16
apt install -y postgresql-15-ddlx # PG 15
apt install -y postgresql-14-ddlx # PG 14
Create Extension:
CREATE EXTENSION ddlx;
Usage
ddlx is a SQL-only extension that generates DDL scripts from PostgreSQL system catalogs. It provides three main functions that accept an OID and work with all reg* object identifier types.
Core Functions
-- Generate CREATE statement for an object
SELECT ddlx_create('my_table'::regclass);
SELECT ddlx_create('my_type'::regtype);
SELECT ddlx_create('my_function'::regproc);
SELECT ddlx_create(current_role::regrole);
-- Generate DROP statement
SELECT ddlx_drop('my_table'::regclass);
-- Generate full DDL script with dependency tree
SELECT ddlx_script('my_table'::regclass);
SELECT ddlx_script('my_enum');
SELECT ddlx_script(current_role::regrole);
Options
Options are passed as a text array (e.g., '{ine,nodcl}'):
SELECT ddlx_create('my_table'::regclass, '{ine}'); -- add IF NOT EXISTS
SELECT ddlx_create('my_type'::regtype, '{noowner}'); -- omit ALTER SET OWNER
SELECT ddlx_script('my_table'::regclass, '{drop}'); -- include DROP statements
Available options: drop, nodrop, owner, noowner, nogrants, nodcl, noalter, ine (IF NOT EXISTS), ie (IF EXISTS), ext, lite, nowrap, nopartitions, comments, nocomments, nostorage, noconstraints, noindexes, nosettings, notriggers, grantor, data.
For Objects Without reg* Types
SELECT ddlx_create(oid) FROM pg_foreign_data_wrapper WHERE fdwname = 'postgres_fdw';
SELECT ddlx_create(oid) FROM pg_database WHERE datname = current_database();
Additional Functions
-- Identify any object by OID
SELECT * FROM ddlx_identify(oid);
-- Describe columns of a class
SELECT * FROM ddlx_describe('my_table'::regclass);
-- Get individual definition parts
SELECT * FROM ddlx_definitions(oid);
-- Generate pre-data creation statements only
SELECT ddlx_createonly('my_table'::regclass);
-- Generate post-data alteration statements
SELECT ddlx_alter('my_table'::regclass);
-- Search function/view bodies by regex
SELECT ddlx_create(objid) FROM ddlx_apropos('users');
-- Get GRANT statements
SELECT ddlx_grants('my_table'::regclass);
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.