使用DBMS_METADATA擷取資料庫物件的中繼資料資訊 - 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，表示找不到對象。