HexaCluster Logo

Introducing pg_dbms_metadata in PostgreSQL for Oracle DBMS_METADATA Compatibility

Introduction: Need for Enhanced DDL Extraction in PostgreSQL like Oracle DBMS_METADATA

In the arena of powerful databases, PostgreSQL stands as a remarkable open-source solution with a wealth of features. PostgreSQL was announced as the DBMS of the Year 2023 and we have also published a summary of PostgreSQL and the achievements in 2023. Yet, amidst its excellence, a crucial enhancement is awaiting – PostgreSQL is seeking a streamlined solution for programmatically extracting DDL for database objects like DBMS_METADATA in Oracle. This inspired us to create pg_dbms_metadata, a solution designed to enhance your PostgreSQL experience and bridge this gap. We are excited to introduce pg_dbms_metadata, a PostgreSQL Extension for Oracle DBMS_METADATA Compatibility.

pg_dbms_metadata_for_oracle_dbms_metadata_compatibility

What is pg_dbms_metadata ?

This extension is designed to give compatibility to Oracle’s DBMS_METADATA package. This facilitates a seamless transition for customers migrating from Oracle to PostgreSQL and provides a systematic approach for effortlessly retrieving DDL programmatically. During the migration from Oracle to PostgreSQL, this extension can help us get easily integrated into the existing workflows, making DDL extraction smoother.

Challenges with Traditional Approaches for DDL extraction in PostgreSQL: A Look at pg_dump

Traditionally, PostgreSQL users have relied on tools like pg_dump to extract DDL for their database objects. Or sometimes, custom scripts or pgAdmin like tools to extract the DDL. While all of these solutions are effective, these approaches lack the flexibility and ease of programmatic extraction. Developers and database administrators often find themselves searching for a more seamless solution that integrates into their existing workflows.

Key Features of pg_dbms_metadata

Following are some of the key features of pg_dbms_metadata.

Multi-Purpose Functionality

The extension not only provides alignment with the Oracle DBMS_METADATA package but also establishes a systematic approach to retrieve DDL programmatically.

Flexible Extraction

Users can generate DDL for an object using a plain SQL query or PL/pgSQL code, providing flexibility and adaptability to different user preferences.

Client-Agnostic Extraction

Unlike traditional methods like pg_dump, the extension : pg_dbms_metadata allows DDL extraction using any client capable of executing plain SQL queries. This opens up new possibilities for integration into various workflows.

Schema Flexibility

Similar to Oracle, users can omit the schema when extracting DDL, leveraging the search_path to locate the object and retrieve the required DDL.

Granular DDL Extraction

The extension provides three essential functions such as following –

GET_DDL(): Extracts DDL for a specified object.
GET_DEPENDENT_DDL(): Extracts DDL for all dependent objects of a specified type for a base object.
GET_GRANTED_DDL(): Extracts SQL statements to recreate granted privileges and roles for a specified grantee.

Configuration Control

Users can customize DDL through session-level transformation parameters using the SET_TRANSFORM_PARAM() procedure.

Usage Examples of pg_dbms_metadata

Following are a few examples demonstrating the usage of the extension.

GET_DDL():

This function extracts DDL of specified database objects.

SELECT dbms_metadata.get_ddl('TABLE','employees','gdmmm');
GET_DEPENDENT_DDL():

This function extracts DDL of all dependent objects of the specified object type for a specified base object.

SELECT dbms_metadata.get_dependent_ddl('CONSTRAINT','employees','gdmmm');
GET_GRANTED_DDL():

This function extracts the SQL statements to recreate granted privileges and roles for a specified grantee.

SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','user_test');
SET_TRANSFORM_PARAM():

This procedure is used to configure session-level transform params, with which we can customize the DDL of objects.

CALL dbms_metadata.set_transform_param('SQLTERMINATOR',true);

Conclusion

In summary, “pg_dbms_metadata” elevates PostgreSQL’s DDL extraction capabilities, offering Oracle compatibility, flexible extraction methods, and granular control over configurations. This extension streamlines workflows, providing a comprehensive solution for seamless migration and enhanced database management in PostgreSQL.

If you are looking to reduce your license spend on Oracle or other commercial databases, Contact HexaCluster today about your options of migrating to PostgreSQL. We are currently offering free Database Migration Assessments for both Oracle, SQL Server and other commercial database users. You may also fill the form below and someone from our team will be in touch with you.

Contact HexaCluster
Please enable JavaScript in your browser to complete this form.
Let us know which of these PostgreSQL Services can help you with today !
Let us know which of these Machine Learning Services we can help you with today !

Author

  • Akhil Reddy Banappagari

    Akhil works at HexaCluster as a Senior Development Manager. He is a skilled developer with expertise in PostgreSQL and Oracle. Akhil specializes in PostgreSQL Development and Database migrations from Oracle and SQL Server to PostgreSQL, and he has successfully managed many complex database migration projects. Akhil is an expert at writing extensions for PostgreSQL. In addition to his role as a database developer, Akhil possesses extensive expertise in creating applications using Python, C, and Java.

Add A Comment

We're glad you have chosen to leave a comment. Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Our PostgreSQL Services

Our Machine Learning Services

Subscribe to our Newsletters

Please enable JavaScript in your browser to complete this form.
I would like to receive Newsletters on following topics :
Enter your email and click on subscribe now, to subscribe to our free newsletters.

Share article

HexaCluster Logo

Get in Touch

© 2023 Company. All Rights Reserved.