pg_dbms_metadata
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_dbms_metadata | 1.0.0 | SIM | PostgreSQL | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 9240 | pg_dbms_metadata | No | No | No | Yes | No | No | dbms_metadata |
| Related | orafce pgtt pg_dbms_lock pg_dbms_job oracle_fdw session_variable pg_statement_rollback ddlx |
|---|
PGDG RPM lacks PG15 on EL8 aarch64; PIGSTY DEB covers PG14-18 on amd64 and arm64.
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 1.0.0 | 1817161514 | pg_dbms_metadata | - |
| RPM | PGDG | 1.0.0 | 1817161514 | pg_dbms_metadata_$v | - |
| DEB | PIGSTY | 1.0.0 | 1817161514 | postgresql-$v-pg-dbms-metadata | - |
Build
You can build the DEB packages for pg_dbms_metadata using pig build:
pig build pkg pg_dbms_metadata # build DEB packages
Install
You can install pg_dbms_metadata 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_dbms_metadata; # Install for current active PG version
pig ext install -y pg_dbms_metadata -v 18 # PG 18
pig ext install -y pg_dbms_metadata -v 17 # PG 17
pig ext install -y pg_dbms_metadata -v 16 # PG 16
pig ext install -y pg_dbms_metadata -v 15 # PG 15
pig ext install -y pg_dbms_metadata -v 14 # PG 14
dnf install -y pg_dbms_metadata_18 # PG 18
dnf install -y pg_dbms_metadata_17 # PG 17
dnf install -y pg_dbms_metadata_16 # PG 16
dnf install -y pg_dbms_metadata_15 # PG 15
dnf install -y pg_dbms_metadata_14 # PG 14
apt install -y postgresql-18-pg-dbms-metadata # PG 18
apt install -y postgresql-17-pg-dbms-metadata # PG 17
apt install -y postgresql-16-pg-dbms-metadata # PG 16
apt install -y postgresql-15-pg-dbms-metadata # PG 15
apt install -y postgresql-14-pg-dbms-metadata # PG 14
Create Extension:
CREATE EXTENSION pg_dbms_metadata;
Usage
pg_dbms_metadata: Extension to add Oracle DBMS_METADATA compatibility to PostgreSQL
Enabling
CREATE EXTENSION pg_dbms_metadata;
GET_DDL
Extract DDL of database objects. Supported types: TABLE, VIEW, SEQUENCE, PROCEDURE, FUNCTION, TRIGGER, INDEX, CONSTRAINT, CHECK_CONSTRAINT, REF_CONSTRAINT, TYPE, ENUM.
SELECT dbms_metadata.get_ddl('TABLE', 'employees', 'public');
SELECT dbms_metadata.get_ddl('VIEW', 'active_users', 'myschema');
SELECT dbms_metadata.get_ddl('FUNCTION', 'calculate_tax', 'public');
SELECT dbms_metadata.get_ddl('INDEX', 'idx_name', 'public');
-- Schema is optional; uses search_path when omitted
SELECT dbms_metadata.get_ddl('SEQUENCE', 'my_seq');
GET_DEPENDENT_DDL
Extract DDL of all dependent objects of a specified type for a base object. Supported: SEQUENCE, TRIGGER, CONSTRAINT, REF_CONSTRAINT, INDEX, ENUM.
SELECT dbms_metadata.get_dependent_ddl('CONSTRAINT', 'employees', 'public');
SELECT dbms_metadata.get_dependent_ddl('INDEX', 'orders', 'sales');
SELECT dbms_metadata.get_dependent_ddl('TRIGGER', 'accounts');
GET_GRANTED_DDL
Extract SQL statements to recreate granted privileges and roles. Supported: ROLE_GRANT.
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'user_test');
SET_TRANSFORM_PARAM
Customize DDL output with session-level transform parameters:
-- Append SQL terminator (;) to each DDL statement
CALL dbms_metadata.set_transform_param('SQLTERMINATOR', true);
-- Exclude constraints from TABLE DDL
CALL dbms_metadata.set_transform_param('CONSTRAINTS', false);
-- Exclude foreign keys from TABLE DDL
CALL dbms_metadata.set_transform_param('REF_CONSTRAINTS', false);
-- Exclude partitioning clauses
CALL dbms_metadata.set_transform_param('PARTITIONING', false);
-- Exclude storage parameters
CALL dbms_metadata.set_transform_param('STORAGE', false);
-- Reset all params to defaults
CALL dbms_metadata.set_transform_param('DEFAULT', true);
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.