pg_dbms_metadata
Extension to add Oracle DBMS_METADATA compatibility to PostgreSQL
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 | Yes | No | Yes | No | No | - |
| Related | orafce pgtt pg_dbms_lock pg_dbms_job oracle_fdw session_variable pg_statement_rollback ddlx |
|---|
pgdg missing el8.aarch64.pg15
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.0.0 | 1817161514 | pg_dbms_metadata | - |
| RPM | PGDG | 1.0.0 | 1817161514 | pg_dbms_metadata_$v | - |
| OS / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
| el8.x86_64 | PGDG 1.0.0 el8.x86_64.pg18 : pg_dbms_metadata_18 pg_dbms_metadata_18-1.0.0-2PGDG.rhel8.noarch.rpm
| PGDG 1.0.0 el8.x86_64.pg17 : pg_dbms_metadata_17 pg_dbms_metadata_17-1.0.0-1PGDG.rhel8.noarch.rpm
| PGDG 1.0.0 el8.x86_64.pg16 : pg_dbms_metadata_16 pg_dbms_metadata_16-1.0.0-1PGDG.rhel8.noarch.rpm
| PGDG 1.0.0 el8.x86_64.pg15 : pg_dbms_metadata_15 pg_dbms_metadata_15-1.0.0-1PGDG.rhel8.noarch.rpm
| PGDG 1.0.0 el8.x86_64.pg14 : pg_dbms_metadata_14 pg_dbms_metadata_14-1.0.0-1PGDG.rhel8.noarch.rpm
|
| el8.aarch64 | PGDG 1.0.0 el8.aarch64.pg18 : pg_dbms_metadata_18 pg_dbms_metadata_18-1.0.0-2PGDG.rhel8.noarch.rpm
| PGDG 1.0.0 el8.aarch64.pg17 : pg_dbms_metadata_17 pg_dbms_metadata_17-1.0.0-1PGDG.rhel8.noarch.rpm
| PGDG 1.0.0 el8.aarch64.pg16 : pg_dbms_metadata_16 pg_dbms_metadata_16-1.0.0-1PGDG.rhel8.noarch.rpm
| PGDG MISS | PGDG 1.0.0 el8.aarch64.pg14 : pg_dbms_metadata_14 pg_dbms_metadata_14-1.0.0-1PGDG.rhel8.noarch.rpm
|
| el9.x86_64 | PGDG 1.0.0 el9.x86_64.pg18 : pg_dbms_metadata_18 pg_dbms_metadata_18-1.0.0-2PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.x86_64.pg17 : pg_dbms_metadata_17 pg_dbms_metadata_17-1.0.0-1PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.x86_64.pg16 : pg_dbms_metadata_16 pg_dbms_metadata_16-1.0.0-1PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.x86_64.pg15 : pg_dbms_metadata_15 pg_dbms_metadata_15-1.0.0-1PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.x86_64.pg14 : pg_dbms_metadata_14 pg_dbms_metadata_14-1.0.0-1PGDG.rhel9.noarch.rpm
|
| el9.aarch64 | PGDG 1.0.0 el9.aarch64.pg18 : pg_dbms_metadata_18 pg_dbms_metadata_18-1.0.0-2PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.aarch64.pg17 : pg_dbms_metadata_17 pg_dbms_metadata_17-1.0.0-1PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.aarch64.pg16 : pg_dbms_metadata_16 pg_dbms_metadata_16-1.0.0-1PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.aarch64.pg15 : pg_dbms_metadata_15 pg_dbms_metadata_15-1.0.0-1PGDG.rhel9.noarch.rpm
| PGDG 1.0.0 el9.aarch64.pg14 : pg_dbms_metadata_14 pg_dbms_metadata_14-1.0.0-1PGDG.rhel9.noarch.rpm
|
| el10.x86_64 | PGDG 1.0.0 el10.x86_64.pg18 : pg_dbms_metadata_18 pg_dbms_metadata_18-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.x86_64.pg17 : pg_dbms_metadata_17 pg_dbms_metadata_17-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.x86_64.pg16 : pg_dbms_metadata_16 pg_dbms_metadata_16-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.x86_64.pg15 : pg_dbms_metadata_15 pg_dbms_metadata_15-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.x86_64.pg14 : pg_dbms_metadata_14 pg_dbms_metadata_14-1.0.0-2PGDG.rhel10.noarch.rpm
|
| el10.aarch64 | PGDG 1.0.0 el10.aarch64.pg18 : pg_dbms_metadata_18 pg_dbms_metadata_18-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.aarch64.pg17 : pg_dbms_metadata_17 pg_dbms_metadata_17-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.aarch64.pg16 : pg_dbms_metadata_16 pg_dbms_metadata_16-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.aarch64.pg15 : pg_dbms_metadata_15 pg_dbms_metadata_15-1.0.0-2PGDG.rhel10.noarch.rpm
| PGDG 1.0.0 el10.aarch64.pg14 : pg_dbms_metadata_14 pg_dbms_metadata_14-1.0.0-2PGDG.rhel10.noarch.rpm
|
| d12.x86_64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| d12.aarch64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| d13.x86_64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| d13.aarch64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| u22.x86_64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| u22.aarch64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| u24.x86_64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
| u24.aarch64 | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS | PGDG MISS |
Install
You can install pg_dbms_metadata directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG 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
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.