DBMS_METADATA - PolarDB - Alibaba Cloud Documentation Center

In PolarDB, the DBMS_METADATA package is used to retrieve metadata for rebuilding objects. Metadata is returned in XML or as DDL statements.

DBMS_METADATA functions and stored procedures

Execute the following statement to install the polar_dbms_metadata plug-in:

CREATE EXTENSION IF NOT EXISTS polar_dbms_metadata;

Function or stored procedure	Type	Return value type	Description
get_ddl	Function	CLOB	Retrieves the metadata of an object as DDL statements.

get_ddl function

The get_ddl function is used to retrieve the DDL statements of an object. The function is defined in the DBMS_METADATA package.

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

Parameter	Description
object_type	The type of the object, such as table. For more information about the supported object types, see Supported object types.
name	The name of the object.
schema	The schema of the object.
version	The version of the metadata.
model	The type of the metadata.
transform	The name of a transformation to be returned.

Note

The get_ddl function in PolarDB for PostgreSQL (Compatible with Oracle) only supports the object_type, name, and schema parameters, but does not support the version, model, and transform parameters. If the version, model, and transform parameters are specified in the function, the parameter values are ignored.

Usage

Examples

The following examples where the object_type parameter is set to table and the schema parameter is set to public retrieve the metadata of the object as DDL statements.

Create the t table in the public schema and call the get_ddl function to retrieve the DDL statements. The object_type parameter is set to table, the name parameter is set to t, and the schema parameter is set to public.

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

Sample result:

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

If the current schema is the schema of the object, you can call the get_ddl without specifying the schema. The function searches the current schema and can retrieve the required DDL statements.

Query current schema.

SELECT current_schema;

Sample result:

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

Query objects on the current schema.

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

Sample result:

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

If the current schema is not the schema of the object, you must specify the schema parameter to find the object definitions when you call the get_ddl function.

Change schema and query object definition.

SET search_path='';
SELECT dbms_metadata.get_ddl('table', 't');

Sample result:

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

Specify schema and query object definition.

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

Sample result:

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

Parameter case-sensitivity

The object_type parameter is case-insensitive. For example, values Table, TABLE, and Table are equivalent.
The name parameter is case-sensitive. For example, if the object name is BIG_t, you cannot enter big_t.
The schema parameter is case-sensitive. For example, if the schema is public, you cannot enter PUBLIC.
In the returned DDL statements, object names and schemas are enclosed double quotation marks are added to the object names and schema names in the obtained DDL statement to ensure case sensitivity.

Examples:

Create test table BIG_t.

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

Check case-sensitivity for parameters.

The object type parameter is case-insensitive. You can specify either table or TABLE.

table

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

Sample result:

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

TABLE

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

Sample result:

                   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. You must enter the correct object name and schema.

Object name

SELECT dbms_metadata.get_ddl('table', 'big_t', 'public');

Sample result:

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

Schema name

SELECT dbms_metadata.get_ddl('table', 'BIG_t', 'PUBLIC');

Sample result:

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

Unsupported schemas

You cannot specify schemas for some object types, because these objects do not belong to specified schemas. For example, if you specify a schema for the role type, the -31600 exception is thrown.

Create a role type object.

SET search_path TO public;
CREATE ROLE role1;

Query the object.

Do not specify the schema name.

SELECT dbms_metadata.get_ddl('role', 'role1');

Sample result:

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

Specify the schema name.

SELECT dbms_metadata.get_ddl('role', 'role1', 'public');

Sample result:

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 some object types such as triggers, you can specify schemas in Oracle, but not in PolarDB, because triggers do not belong to a schema. For the purpose of compatibility with Oracle, no exception is thrown when specifying a schema for a trigger. Only a warning message is generated, prompting that the specified schema is ignored. The result is returned.

Create a test trigger.

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();

Query the trigger.

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

Sample result:

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)

Supported object types

The following table describes the object types for which you can retrieve DDL statements.

Object type	Whether schemas can be specified
Index	Yes.
View	Yes.
Materialized view	Yes.
Function	Yes.
Stored procedure	Yes.
Trigger	No.
Constraint	Yes.
Table	Yes.
Tablespace	No.
Role	No.
User	No. This object type is similar to role.

Exceptions

The get_ddl function in PolarDB involves the following exception types.

Note

For the two exception types, the exception codes are the same as those in Oracle, and the exception messages are nearly the same. Other exception types are not supported.

When the object type is invalid, the object type is empty, or the object name is empty, an exception with code-31600 is thrown.
When the object cannot be found, an exception with code -31603 is thrown.