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"         +
     )
    (1 row)
  • 如果当前处于对象所在的模式下,可以在调用get_ddl时不指定模式,函数会去当前模式下查找对象,同样也可以查找到对应的DDL语句。
    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"         +
     )
    (1 row)
  • 如果切换了模式,不再处于对象所在的模式下,不指定模式是无法找到对象定义的,此时必须指定模式才能找到对象的定义。
    set search_path='';
    select dbms_metadata.get_ddl('table', 't');
    
    ERROR:  Polar-31603: Object "t" of type "table" not found in schema "<NULL>"
    
    select dbms_metadata.get_ddl('table', 't', 'public');
    
                    get_ddl
    ---------------------------------------
     CREATE TABLE IF NOT EXISTS public.t (+
         a integer,                       +
         b text COLLATE "default"         +
     )
    (1 row)

参数大小写

  • 对象类型是大小写不敏感的,以表类型为例,table、TABLE、Table是等价的。
  • 对象名是大小写敏感的,如果对象名为BIG_t,使用big_t则无法查到该对象。
  • 模式名是大小写敏感的,如果模式为public,使用PUBLIC则无法查到该对象。
  • 对于大写的对象名和模式名,获取的DDL中会在对象名和模式名上加双引号,以保证大小写敏感。
示例:
create table "BIG_t"("BIG_a" int, "BIG_b" text);

# 对象类型大小写不敏感: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"         +
 )
(1 row)

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"         +
 )
(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号异常。
    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 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 each row execute procedure 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,表示找不到对象。