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.
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.
Subscribe to our Newsletters and stay tuned for more interesting topics.
[…] PostgreSQL extension to extract DDL of database objects in a way compatible with the Oracle DBMS_METADATA package. 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. […]