PolarDB:DBMS_METADATA

Syntax

FUNCTION get_ddl(
    object_type IN VARCHAR2,
    name        IN VARCHAR2,
    schema      IN VARCHAR2 DEFAULT NULL,
    version     IN VARCHAR2 DEFAULT 'compatible',
    model       IN VARCHAR2 DEFAULT 'polardb',
    transform   IN VARCHAR2 DEFAULT 'ddl'
) RETURN CLOB

Parameters

PolarDB for PostgreSQL (Compatible with Oracle) only processes object_type, name, and schema. The version, model, and transform parameters are accepted for Oracle compatibility but have no effect.

Parameter case sensitivity

In the returned DDL, object names and schema names are enclosed in double quotation marks to preserve case.

Examples

Retrieve DDL for a table in a specific schema

Create a table and call get_ddl with the schema explicitly specified:

CREATE TABLE t(a int, b text);
SELECT dbms_metadata.get_ddl('table', 't', 'public');

Output:

get_ddl
---------------------------------------
 CREATE TABLE IF NOT EXISTS public.t (+
     a integer,                       +
     b text COLLATE "default"         +
 )                                    +
 WITH (oids = true)
(1 row)

Retrieve DDL without specifying the schema

When the target object is in the current schema, omit the schema parameter:

SELECT current_schema;

current_schema
----------------
 public
(1 row)

SELECT dbms_metadata.get_ddl('table', 't');

get_ddl
---------------------------------------
 CREATE TABLE IF NOT EXISTS public.t (+
     a integer,                       +
     b text COLLATE "default"         +
 )                                    +
 WITH (oids = true)
(1 row)

Retrieve DDL when the object is in a different schema

If the current schema does not contain the object, omitting schema causes a -31603 error. Specify schema explicitly:

-- Change the current schema
SET search_path='';

-- Without schema: fails
SELECT dbms_metadata.get_ddl('table', 't');

ERROR:  Polar-31603: Object "t" of type "table" not found in schema "<NULL>"

-- With schema: succeeds
SELECT dbms_metadata.get_ddl('table', 't', 'public');

get_ddl
---------------------------------------
 CREATE TABLE IF NOT EXISTS public.t (+
     a integer,                       +
     b text COLLATE "default"         +
 )                                    +
 WITH (oids = true)
(1 row)

Case sensitivity for `name` and `schema`

Create a table with mixed-case identifiers:

CREATE TABLE public."BIG_t"("BIG_a" int, "BIG_b" text);

The object_type parameter is case-insensitive — both 'table' and 'TABLE' work:

SELECT dbms_metadata.get_ddl('table', 'BIG_t', 'public');
SELECT dbms_metadata.get_ddl('TABLE', 'BIG_t', 'public');

Both return:

get_ddl
---------------------------------------------
 CREATE TABLE IF NOT EXISTS public."BIG_t" (+
     "BIG_a" integer,                       +
     "BIG_b" text COLLATE "default"         +
 )                                          +
 WITH (oids = true)
(1 row)

The name and schema parameters are case-sensitive. Using the wrong case returns an error:

-- Wrong name case
SELECT dbms_metadata.get_ddl('table', 'big_t', 'public');

ERROR:  Polar-31603: Object "big_t" of type "table" not found in schema "public"

-- Wrong schema case
SELECT dbms_metadata.get_ddl('table', 'BIG_t', 'PUBLIC');

ERROR:  Polar-31603: Object "BIG_t" of type "table" not found in schema "PUBLIC"

Schema behavior for schema-less object types

For role and user, specifying a schema throws a -31600 exception:

SET search_path TO public;
CREATE ROLE role1;

-- Without schema: succeeds
SELECT dbms_metadata.get_ddl('role', 'role1');

get_ddl
-------------------------------------------
 CREATE ROLE role1 WITH                   +
 NOSUPERUSER NOCREATEDB NOCREATEROLE      +
 INHERIT NOLOGIN NOREPLICATION NOBYPASSRLS+
 CONNECTION LIMIT -1 PASSWORD NULL
(1 row)

-- With schema: throws -31600
SELECT dbms_metadata.get_ddl('role', 'role1', 'public');

ERROR:  Polar-31600: Invalid input value "public" for parameter SCHEMA in function get_ddl
DETAIL:  No need to specify schema for type ROLE/USER

For trigger, specifying a schema generates a warning but does not throw an error. The schema value is ignored and the result is returned:

CREATE TABLE t (id int, name varchar(10));
CREATE OR REPLACE FUNCTION print_insert()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'INSERT: %', NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger1 after INSERT ON public.t FOR EACH row EXECUTE PROCEDURE print_insert();

SELECT dbms_metadata.get_ddl('trigger', 'trigger1', 'public');

WARNING:  No need to specify schema for trigger, ignore it.
                                                        get_ddl
-------------------------------------------------------------------------------------------------------
 CREATE TRIGGER trigger1 AFTER INSERT ON public.t FOR EACH ROW EXECUTE PROCEDURE public.print_insert()
(1 row)