使用DBMS_METADATA获取数据库对象的DDL语句-云原生数据库 PolarDB-阿里云

PolarDB通过DBMS_METADATA提供的方法获取数据库对象的元数据信息，用于重建数据库中的对象。元数据信息以XML形式或者DDL语句的形式返回。

DBMS_METADATA函数/存储过程

执行以下命令安装polar_dbms_metadata插件：

CREATE EXTENSION IF NOT EXISTS polar_dbms_metadata;

函数/存储过程	类型	返回类型	描述
get_ddl	函数	CLOB	用于获取对象的DDL。

get_ddl函数

get_ddl函数用于获取对象的DDL（Data Definition Language，数据库模式定义语言），函数定义在DBMS_METADATA包中。

语法

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

参数说明

参数	说明
object_type	对象的类型，例如，table。支持的对象类型，请参见支持的类型。
name	对象的名称。
schema	对象所属的模式。
version	对象的元数据版本。
model	元数据的类型。
transform	用于修改对象。

说明

目前PolarDB PostgreSQL版（兼容Oracle）的get_ddl函数只支持object_type、name、schema参数，而不支持version、model、transform参数。如果调用时指定了后三个参数，参数值将会被忽略。

使用指南

基本用法

以table类型和public模式（schema）为例，介绍如何使用get_ddl获取对象的DDL语句。

在public模式下创建表t，然后使用get_ddl获取其DDL语句。其中，对象类型为table，对象名为t，对象所属的模式为public。

CREATE TABLE t(a int, b text);
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)

如果当前处于对象所在的模式下，可以在调用get_ddl时不指定模式，函数会在当前模式下查找对象，同样也可以查找到对应的DDL语句。

查看当前Schema。

SELECT current_schema;

返回结果如下：

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

在当前Schema上查找对象。

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)

如果切换了模式，不再处于对象所在的模式下，不指定模式是无法找到对象定义的，此时必须指定模式才能找到对象的定义。

切换Schema，查询对象定义。

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

返回结果如下：

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

指定Schema，查询对象定义。

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)

参数大小写

对象类型是大小写不敏感的，以表类型为例，table、TABLE、Table是等价的。
对象名是大小写敏感的，如果对象名为BIG_t，使用big_t则无法查到该对象。
模式名是大小写敏感的，如果模式为public，使用PUBLIC则无法查到该对象。
对于大写的对象名和模式名，获取的DDL中会在对象名和模式名上加双引号，以保证大小写敏感。

示例：

准备测试表BIG_t。

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

不同参数的大小写敏感。

对象类型大小写不敏感：table/TABLE均可。

table

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

返回结果如下：

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

返回结果如下：

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

对象名、模式名大小写敏感，大小写错误就会导致无法找到对象。

对象名

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

返回结果如下：

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

模式名

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

返回结果如下：

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

不支持模式的类型

对于部分对象，无法指定模式名，因为这些对象不属于某个模式。以role类型为例，对其指定模式名时，会抛出-31600号异常。

创建role类型。

SET search_path TO public;
CREATE ROLE role1;

查询对象。

不指定模式名。

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)

指定模式名。

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

对于部分类型，例如触发器，在Oracle中可以指定模式名，但是在PolarDB中无法指定模式名，因为触发器不属于某个模式。为了与Oracle保持兼容，在指定模式名时不会报错，只打印一条警告，提示该模式名被忽略，然后继续返回结果。

创建测试触发器。

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)

支持的类型

当前版本支持获取DDL的对象类型如下：

类型	特殊说明（是否支持指定schema）
索引（index）	支持指定schema。
视图（view）	支持指定schema。
物化视图（materialized_view）	支持指定schema。
函数（function）	支持指定schema。
存储过程（procedure）	支持指定schema。
触发器（trigger）	不支持指定schema。
约束（constraint）	支持指定schema。
表（table）	支持指定schema。
表空间（tablespace）	不支持指定schema。
角色（role）	不支持指定schema。
用户（user）	不支持指定schema，与role基本一致。

异常信息

PolarDB的get_ddl函数主要有以下两类常见的异常。

说明

在以下两类场景下抛出的异常号与Oracle相同，异常提示信息也基本相同。其他异常类型暂时不支持。

出现对象类型错误、对象类型为空、对象名为空等错误时，抛出异常，错误码为-31600，表示参数异常。
出现对象无法找到时，抛出异常，错误码为-31603，表示找不到对象。