依賴視圖DDL修改最佳實務 - AnalyticDB

本文介紹了在AnalyticDB for PostgreSQL中，修改被視圖依賴的基表和視圖時面臨的限制，分析此類修改對依賴視圖產生的影響，以及如何快速且安全地修改某個基表結構或視圖。

注意事項

本功能僅支援AnalyticDB PostgreSQL 7.0版執行個體。
本文涉及的視圖均為依賴於基表的依賴視圖。

查看基表和視圖的依賴關係

若您要查看基表和視圖的依賴關係，需要建立查詢依賴資訊的函數，以如下圖所示的基表和視圖為例，為您介紹如何查看基表和視圖的依賴關係。

使用如下SQL語句建立上圖基表和依賴視圖。

CREATE TABLE cities(id INT, name TEXT, info TEXT);
CREATE TABLE products(id INT, info TEXT);
CREATE TABLE sales(product_id INT, city_id INT, money NUMERIC(10,2));
CREATE VIEW all_info AS SELECT * FROM cities c JOIN sales s ON c.id = s.city_id;
CREATE VIEW ps_info AS SELECT * FROM products p JOIN sales s ON p.id = s.product_id;
CREATE VIEW psc_info AS SELECT * FROM  all_info a JOIN ps_info p ON p.product_id = s.product_id;

使用如下所示SQL語句建立用於查詢依賴資訊的函數。

CREATE OR REPLACE FUNCTION get_table_dependencies(input_schema TEXT, input_table TEXT)
RETURNS TABLE(
    depth INT, 
    dependent_schema TEXT,
    dependent_view TEXT,
    dependent_oid OID,
    source_schema TEXT,
    source_table TEXT
) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE find_views AS (
      SELECT DISTINCT                               
        n.nspname::TEXT as dependent_schema,    -- 確保列是TEXT類型
        cl.relname::TEXT as dependent_view,    -- 轉換為TEXT類型
        cl.oid as dependent_oid,  -- 將OID轉換為TEXT
        nb.nspname::TEXT as source_schema,    -- 轉換為TEXT類型
        c.relname::TEXT as source_table,     -- 轉換為TEXT類型
        1 as depth
      FROM
        pg_depend d
        JOIN pg_rewrite r ON d.objid = r.oid
        JOIN pg_class cl ON r.ev_class = cl.oid
        JOIN pg_class c ON d.refobjid = c.oid
        JOIN pg_namespace n ON cl.relnamespace = n.oid
        JOIN pg_namespace nb ON c.relnamespace = nb.oid
      WHERE
        d.deptype = 'n'
        AND d.classid='pg_rewrite'::regclass
        AND cl.relkind IN ('v', 'm') 
        AND nb.nspname = input_schema
        AND c.relname = input_table
      UNION ALL
        SELECT                               
          n.nspname::TEXT,
          cl.relname::TEXT,
          cl.oid,
          nb.nspname::TEXT,
          c.relname::TEXT,
          fv.depth + 1
        FROM
          pg_depend d
          JOIN pg_rewrite r ON d.objid = r.oid
          JOIN pg_class cl ON r.ev_class = cl.oid
          JOIN pg_class c ON d.refobjid = c.oid
          JOIN pg_namespace n ON cl.relnamespace = n.oid
          JOIN pg_namespace nb ON c.relnamespace = nb.oid
          JOIN find_views fv ON fv.dependent_oid = c.oid
        WHERE
          d.deptype = 'n'
          AND d.classid='pg_rewrite'::regclass
          AND cl.relkind IN ('v', 'm') 
          AND cl.oid <> c.oid
    )
    SELECT DISTINCT fv.depth, fv.dependent_schema, fv.dependent_view, fv.dependent_oid, fv.source_schema, fv.source_table
    FROM find_views fv
    ORDER BY fv.depth;
END;
$$ LANGUAGE plpgsql;

參數說明如下。

參數	說明

參數	說明
depth	對中間視圖或基表的依賴層級，直接依賴基表的視圖depth為 1。
dependent_schema	查詢出來的依賴視圖所在的schema。
dependent_view	查詢出來的依賴視圖名。
dependent_oid	依賴視圖的oid。
source_schema	被依賴表或視圖所在的 schema。
source_table	被依賴表或依賴視圖的名稱。

使用如下SQL語句即可查詢上圖基表與依賴視圖的關係。

AS SELECT * FROM get_table_dependencies('public', 'sales');

 depth | dependent_schema | dependent_view | dependent_oid | source_schema | source_table 
-------+------------------+----------------+---------------+---------------+--------------
     1 | public           | all_info       |         26635 | public        | sales
     1 | public           | ps_info        |         26644 | public        | sales
     2 | public           | psc_info       |         26648 | public        | all_info
     2 | public           | psc_info       |         26648 | public        | ps_info
(4 ROWS)

修改基表或視圖的限制

修改基表的限制

修改基表中被引用的資料類型，視圖資料類型無法被識別。
建立依賴視圖時，視圖每一列的類型已經被記錄在系統資料表中，因此如果修改基表中被引用的資料類型，依賴視圖對應列的資料類型無法被識別，沒有被引用的基表欄位則不受影響。
修改基表中被引用的列，視圖列名不會改變。
建立依賴視圖時，系統資料表中儲存的是基表列名的序號依賴。修改基表列名不會影響依賴判斷，因此調整基表列名後，依賴視圖的列名不會隨之更改，原列名的序號關係已經在系統資料表中被記錄。
刪除基表中被引用的列，視圖會被刪除。
若使用Cascade串聯刪除，會將引用該列的依賴視圖一併刪除。

更多基表DDL限制，請參見下表。

ALTER DDL	有無影響	備忘

ALTER DDL	有無影響	備忘
RENAME	無影響	查詢依賴視圖的定義時，被依賴的基表或中間視圖的重新命名會隨之更新。
RENAME COLUMN	無影響	查詢依賴視圖定義時，對應的SELECT列更新，但對應視圖本身的列名不會修改。
RENAME CONSTRAINT	無影響	重新命名約束。
SET SCHEMA	無影響	查詢依賴視圖的定義時，會自動更新SCHEMA資訊。
ATTACH PARTITION	無影響	無
DETACH PARTITION	無影響	無
DROP COLUMN 引用列	有影響	串聯刪除直接刪除視圖。
ALTER COLUMN TYPE	有影響	文法層級限制。
ALTER COLUMN SET DEFAULT	無影響	無
ALTER COLUMN DROP DEFAULT	無影響	無
ALTER COLUMN SET/DROP NOT NULL	無影響	無
ALTER COLUMN ADD/SET/DROP GENERATED AS IDENTITY	無影響	將某列轉換為自增列。
ALTER COLUMN SET STATISTICS	無影響	無
ALTER COLUMN RESET/SET ( attribute_option = value)	無影響	無
ALTER COLUMN SET STORAGE	無影響	無
ADD table_constraint	無影響	無
DROP table_constraint	無影響	無
ADD table_constraint_using_index	無影響	無
ENABLE TRIGGER	無影響	無
ENABLE RULE	無影響	無
INHERIT	無影響	無
REDACTION POLICY	無影響	取決於最終查詢人的許可權。

修改視圖的限制

視圖的修改與基表不同，針對視圖的ALTER命令僅能用於修改列的DEFAULT值，常見的修改方式是使用CREATE OR REPLACE的方式對視圖重建。

禁止新增或刪除視圖的任何列。
新增或刪除視圖的任何列相當於重新更新了視圖定義，就算該列沒有被引用，列與基表對應的序號關係也改變了，這會導致系統資料表中記錄的依賴關係不可用，所以該行為被禁止。
允許新增視圖的追加列。
允許使用CREATE OR REPLACE新增視圖的追加列，追加列對已有列順序沒有影響，依賴的視圖仍舊可以通過系統資料表資訊找到對應的列，因此該操作可正常執行。
禁止刪除視圖尾列。
視圖禁止刪除尾列。
禁止修改視圖某一列的資料類型。
視圖不允許修改欄欄位名。

更多視圖修改限制，請參見下表。

結構變更	有無影響	備忘

結構變更	有無影響	備忘
新增某一中間列	有影響	直接修改中間視圖定義來新增列，且有視圖依賴該中間視圖時會被禁止。
新增追加列	無影響	無
刪除列	有影響	直接修改中間視圖定義來刪除列，且有視圖依賴該中間視圖時會被禁止。
修改引用列資料類型	有影響	沒有被引用的欄位沒有影響。
修改欄位名	有影響	中間視圖不允許修改欄欄位名。

修改基表或視圖

修改步驟

匯出視圖相關DDL（根據實際情況選擇下面一種方式）。
- 修改public.test 基表或中間視圖列b的資料類型：執行 SELECT record_dependency_ddl('public','test','b')匯出相關依賴視圖的DDL資訊。
- 刪除public.test 基表引用列 b：執行SELECT record_dependency_ddl ('public','test','b') 匯出相關依賴視圖的DDL資訊。
- 在中間視圖的非末尾處增加列：擷取與該視圖相關的所有依賴視圖，執行SELECT record_dependency_ddl('public','test','')匯出相關依賴視圖的DDL資訊。
- 在中間視圖中刪除列：擷取與該視圖相關的所有依賴視圖，執行SELECT record_dependency_ddl('public','test','')匯出相關依賴視圖的DDL資訊。
- 修改中間視圖的某欄欄位名：擷取與該視圖相關的所有依賴視圖，執行SELECT record_dependency_ddl('public','test','')匯出相關依賴視圖的DDL資訊。
- DROP DDL和CREATE DDL：DROP DDL可以在暫存資料表temp_drop_dependency_ddl查看，CREATE DDL可在temp_create_dependency_ddl查看。
- 執行record_dependency_ddl函數：每次執行record_dependency_ddl函數，暫存資料表資訊都將被重新整理。暫存資料表只存在於當前session中，斷連或切換資料庫都將消失。
執行DROP命令。
使用SELECT drop_dependency_table()來執行DROP DDL。若DROP失敗，可嘗試查看暫存資料表temp_drop_dependency_ddl並手動執行。
執行基表類型修改或新增列修改。
執行CREATE命令（根據實際情況選擇下面的一種方式）。
- 修改public.test 基表或中間視圖列b的資料類型：使用SELECT create_dependency_table()執行CREATE DDL。若CREATE失敗，可嘗試查看暫存資料表temp_create_dependency_ddl並手動執行。
- 對public.test 基表刪除引用列b：查看暫存資料表temp_create_dependency_ddl ，修改引用列b的依賴視圖DDL並手動執行。
- 在中間視圖的非末尾處增加列：使用SELECT create_dependency_table()執行CREATE ddl，若CREATE失敗，可嘗試查看暫存資料表temp_create_dependency_ddl並手動執行。
- 在中間視圖中刪除列：該列未被其它視圖依賴，使用SELECT create_dependency_table()執行CREATE DDL，若CREATE失敗，可嘗試查看暫存資料表temp_create_dependency_ddl並手動執行。
- 在中間視圖中刪除列：該列被其它視圖依賴，查看暫存資料表temp_create_dependency_ddl ，修改引用該列的依賴視圖的DDL資訊並手動執行。
- 修改中間視圖的某欄欄位名：該列未被其它視圖依賴，使用SELECT create_dependency_table()執行CREATE DDL，若CREATE失敗，可嘗試查看暫存資料表temp_create_dependency_ddl並手動執行。
- 修改中間視圖的某欄欄位名：該列被其它視圖依賴，查看暫存資料表temp_create_dependency_ddl ，修改引用該列的依賴視圖的DDL資訊並手動執行。

使用樣本

以如下圖所示的基表和視圖為例，為您舉例如何安全修改基表和視圖。

執行如下SQL命令建立基表和視圖。

CREATE TABLE test(a INT, b INT, c INT);
CREATE VIEW v1 AS SELECT a, b FROM test;
CREATE VIEW  v2 AS SELECT b, c FROM test;
CREATE VIEW  v3 AS SELECT test.a, v2.c FROM test JOIN v2 ON test.b = v2.b;

 List OF relations
 Schema | Name | Type  |   Owner    | Storage 
--------+------+-------+------------+---------
 public | test | TABLE | adbpgadmin | heap
 public | v1   | VIEW  | adbpgadmin | 
 public | v2   | VIEW  | adbpgadmin | 
 public | v3   | VIEW  | adbpgadmin | 
(4 ROWS)

建立修改基表或視圖的函數，該函數在修改基表或視圖之前會刪除所有依賴視圖並記錄依賴視圖的DDL，修改完成後可以直接重建依賴視圖。具體函數及操作如下。

CREATE OR REPLACE FUNCTION public.record_dependency_ddl(schema_name TEXT, table_name TEXT, column_name TEXT)
RETURNS VOID AS $$
DECLARE 
    view_info RECORD;
    combine_ddl TEXT := '';
    drop_ddl TEXT := '---- DROP VIEW ---' || E'\n';
    drop_str TEXT;
    full_name TEXT;
    ddl_line TEXT;
BEGIN 
    CREATE TEMP TABLE IF NOT EXISTS temp_drop_dependency_ddl (
        ddl_statement TEXT NOT NULL
    );

    CREATE TEMP TABLE IF NOT EXISTS temp_create_dependency_ddl (
        ddl_statement TEXT NOT NULL
    );

    TRUNCATE temp_drop_dependency_ddl;
    TRUNCATE temp_create_dependency_ddl;

    IF column_name != '' THEN
        FOR view_info IN
            WITH RECURSIVE find_views AS (
            SELECT DISTINCT                               
            n.nspname AS dependent_schema,
            cl.relname AS dependent_view,
            cl.oid AS dependent_oid,
            cl.relkind AS dependent_type,
            nb.nspname AS source_schema,
            c.relname AS source_table,
            1 AS depth
            FROM
            pg_depend d
            JOIN pg_rewrite r ON d.objid = r.oid
            JOIN pg_class cl ON r.ev_class = cl.oid
            JOIN pg_class c ON d.refobjid = c.oid
            JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = d.refobjsubid
            JOIN pg_namespace n ON cl.relnamespace = n.oid
            JOIN pg_namespace nb ON c.relnamespace = nb.oid
            WHERE
            d.deptype = 'n'
            AND d.classid='pg_rewrite'::regclass
            AND (cl.relkind = 'v' OR cl.relkind = 'm')  -- 'v' means the source object is a view
            AND nb.nspname = schema_name  -- schema of the table
            AND c.relname = table_name -- name of the table
            AND a.attname = column_name -- name of the column
            UNION ALL
            SELECT                               
            n.nspname,
            cl.relname,
            cl.oid,
            cl.relkind,
            nb.nspname,
            c.relname,
            fv.depth + 1
            FROM
            pg_depend d
            JOIN pg_rewrite r ON d.objid = r.oid
            JOIN pg_class cl ON r.ev_class = cl.oid
            JOIN pg_class c ON d.refobjid = c.oid
            JOIN pg_namespace n ON cl.relnamespace = n.oid
            JOIN pg_namespace nb ON c.relnamespace = nb.oid
            JOIN find_views fv ON fv.dependent_oid = c.oid
            WHERE
            d.deptype = 'n'
            AND d.classid='pg_rewrite'::regclass
            AND (cl.relkind = 'v' OR cl.relkind = 'm')  -- 'v' means the source object is a view
            AND cl.oid <> c.oid
            )
            
            SELECT DISTINCT depth, dependent_schema, dependent_view, dependent_type
            FROM find_views
            ORDER BY depth
        LOOP

            full_name := view_info.dependent_schema || '.' || view_info.dependent_view;
            FOR ddl_line IN
                SELECT dump_table_ddl(full_name)
            LOOP
                IF ddl_line LIKE 'CREATE MATERIALIZED VIEW%' THEN
                    ddl_line := REPLACE(ddl_line, 'VIEW', 'VIEW IF NOT EXISTS');
                ELSIF ddl_line LIKE 'CREATE VIEW%' THEN
                    ddl_line := REPLACE(ddl_line, 'CREATE VIEW', 'CREATE OR REPLACE VIEW');
                END IF;
                combine_ddl := combine_ddl || ddl_line || E'\n';
            END LOOP;
            
            IF view_info.depth = 1 THEN
                drop_str := 'DROP VIEW ';
                IF view_info.dependent_type = 'm' THEN
                    drop_str := 'DROP MATERIALIZED VIEW ';
                END IF;
                drop_ddl := drop_ddl || drop_str || 'IF EXISTS ' || full_name || ' CASCADE;' || E'\n';
            END IF;
        END LOOP;
    ELSE 
        FOR view_info IN
            WITH RECURSIVE find_views AS (
            SELECT DISTINCT                               
            n.nspname AS dependent_schema,
            cl.relname AS dependent_view,
            cl.oid AS dependent_oid,
            cl.relkind AS dependent_type,
            nb.nspname AS source_schema,
            c.relname AS source_table,
            1 AS depth
            FROM
            pg_depend d
            JOIN pg_rewrite r ON d.objid = r.oid
            JOIN pg_class cl ON r.ev_class = cl.oid
            JOIN pg_class c ON d.refobjid = c.oid
            JOIN pg_namespace n ON cl.relnamespace = n.oid
            JOIN pg_namespace nb ON c.relnamespace = nb.oid
            WHERE
            d.deptype = 'n'
            AND d.classid='pg_rewrite'::regclass
            AND (cl.relkind = 'v' OR cl.relkind = 'm')  -- 'v' means the source object is a view
            AND nb.nspname = schema_name  -- schema of the table
            AND c.relname = table_name -- name of the table
            UNION ALL
            SELECT                               
            n.nspname,
            cl.relname,
            cl.oid,
            cl.relkind,
            nb.nspname,
            c.relname,
            fv.depth + 1
            FROM
            pg_depend d
            JOIN pg_rewrite r ON d.objid = r.oid
            JOIN pg_class cl ON r.ev_class = cl.oid
            JOIN pg_class c ON d.refobjid = c.oid
            JOIN pg_namespace n ON cl.relnamespace = n.oid
            JOIN pg_namespace nb ON c.relnamespace = nb.oid
            JOIN find_views fv ON fv.dependent_oid = c.oid
            WHERE
            d.deptype = 'n'
            AND d.classid='pg_rewrite'::regclass
            AND (cl.relkind = 'v' OR cl.relkind = 'm')  -- 'v' means the source object is a view
            AND cl.oid <> c.oid
            )
            
            SELECT DISTINCT depth, dependent_schema, dependent_view, dependent_type
            FROM find_views
            ORDER BY depth
        LOOP

            full_name := view_info.dependent_schema || '.' || view_info.dependent_view;
            FOR ddl_line IN
                SELECT dump_table_ddl(full_name)
            LOOP
                IF ddl_line LIKE 'CREATE MATERIALIZED VIEW%' THEN
                    ddl_line := REPLACE(ddl_line, 'VIEW', 'VIEW IF NOT EXISTS');
                ELSIF ddl_line LIKE 'CREATE VIEW%' THEN
                    ddl_line := REPLACE(ddl_line, 'CREATE VIEW', 'CREATE OR REPLACE VIEW');
                END IF;
                combine_ddl := combine_ddl || ddl_line || E'\n';
            END LOOP;
            
            IF view_info.depth = 1 THEN
                drop_str := 'DROP VIEW ';
                IF view_info.dependent_type = 'm' THEN
                    drop_str := 'DROP MATERIALIZED VIEW ';
                END IF;
                drop_ddl := drop_ddl || drop_str || 'IF EXISTS ' || full_name || ' CASCADE;' || E'\n';
            END IF;
        END LOOP;
    END IF;
  
    INSERT INTO temp_drop_dependency_ddl VALUES (drop_ddl);
    INSERT INTO temp_create_dependency_ddl VALUES (combine_ddl);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.drop_dependency_table()
RETURNS VOID AS
$$
DECLARE
    ddl_text TEXT;
BEGIN
    FOR ddl_text IN SELECT ddl_statement FROM temp_drop_dependency_ddl
    LOOP
        EXECUTE ddl_text;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.create_dependency_table()
RETURNS VOID AS
$$
DECLARE
    ddl_text TEXT;
BEGIN
    FOR ddl_text IN SELECT ddl_statement FROM temp_create_dependency_ddl
    LOOP
        EXECUTE ddl_text;
    END LOOP;

    SET search_path TO PUBLIC;
END;
$$
LANGUAGE plpgsql;

說明

這些函數只能用於依賴視圖定義不會改變的情況下，如果需要同時修改依賴視圖，需要在匯出的DDL基礎上進行手動修改與重建。

執行record_dependency_ddl函數用於記錄依賴視圖的DROP命令資訊和CREATE命令資訊，這些命令分別儲存在temp_drop_dependency_ddl暫存資料表和temp_create_dependency_ddl暫存資料表中。如下所示為查詢依賴於public.test表b列的視圖。

SELECT record_dependency_ddl('public','test','b');
NOTICE:  TABLE doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ddl_statement' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution OF data. Make sure COLUMN(s) chosen ARE the optimal data distribution key TO minimize skew.
NOTICE:  TABLE doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ddl_statement' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution OF data. Make sure COLUMN(s) chosen ARE the optimal data distribution key TO minimize skew.
 record_dependency_ddl 
-----------------------
(1 row)

SELECT * FROM temp_drop_dependency_ddl ;
ddl_statement              
----------------------------------------
 ---- DROP VIEW ---                    +
 DROP VIEW IF EXISTS public.v1 CASCADE;+
 DROP VIEW IF EXISTS public.v2 CASCADE;+
 DROP VIEW IF EXISTS public.v3 CASCADE;+
 
(1 ROW)

SELECT * FROM temp_create_dependency_ddl ;
ddl_statement                        
------------------------------------------------------------
 --                                                       +
 -- Greenplum Database database dump                       +
 --                                                        +                                                     
 -- Dumped from database version 12.12                     +
 -- Dumped by pg_dump version 12.12                        +                                                     
 SET gp_default_storage_options = '';                      +
 SET statement_timeout = 0;                                +
 SET lock_timeout = 0;                                     +
 SET idle_in_transaction_session_timeout = 0;              +
 SET idle_session_timeout = 0;                             +
 SET client_encoding = 'UTF8';                             +
 SET standard_conforming_strings = on;                     +
 SELECT pg_catalog.set_config('search_path', '', false);   +
 SET check_function_bodies = false;                        +
 SET xmloption = content;                                  +
 SET client_min_messages = warning;                        +
 SET row_security = off;                                   +                                                      
 --                                                        +
 -- Name: v1; Type: VIEW; Schema: public; Owner: adbpgadmin+
 --                                                        +                                                           
 CREATE OR REPLACE VIEW public.v1 AS                       +
  SELECT test.a,                                           +
     test.b                                                +
    FROM public.test;                                      +
                                                           +                                                   
 ALTER TABLE public.v1 OWNER TO adbpgadmin;                +
                                                           +
 --                                                        +
 -- Greenplum Database database dump complete              +
 --                                                        +                                                                                                                 +
 --                                                        +
 -- Greenplum Database database dump                       +
 --                                                        +                                                        
 -- Dumped from database version 12.12                     +
 -- Dumped by pg_dump version 12.12                        +                                                       
 SET gp_default_storage_options = '';                      +
 SET statement_timeout = 0;                                +
 SET lock_timeout = 0;                                     +
 SET idle_in_transaction_session_timeout = 0;              +
 SET idle_session_timeout = 0;                             +
 SET client_encoding = 'UTF8';                             +
 SET standard_conforming_strings = on;                     +
 SELECT pg_catalog.set_config('search_path', '', false);   +
 SET check_function_bodies = false;                        +
 SET xmloption = content;                                  +
 SET client_min_messages = warning;                        +
 SET row_security = off;                                   +                                                    
 --                                                        +
 -- Name: v2; Type: VIEW; Schema: public; Owner: adbpgadmin+
 --                                                        +                                                       
 CREATE OR REPLACE VIEW public.v2 AS                       +
  SELECT test.b,                                           +
     test.c                                                +
    FROM public.test;                                      +
                                                           +                                                     
 ALTER TABLE public.v2 OWNER TO adbpgadmin;                +                                                        
 --                                                        +
 -- Greenplum Database database dump complete              +
 --                                                        +                                                                                                    +
 --                                                        +
 -- Greenplum Database database dump                       +
 --                                                        +                                                     
 -- Dumped from database version 12.12                     +
 -- Dumped by pg_dump version 12.12                        +                                                    
 SET gp_default_storage_options = '';                      +
 SET statement_timeout = 0;                                +
 SET lock_timeout = 0;                                     +
 SET idle_in_transaction_session_timeout = 0;              +
 SET idle_session_timeout = 0;                             +
 SET client_encoding = 'UTF8';                             +
 SET standard_conforming_strings = on;                     +
 SELECT pg_catalog.set_config('search_path', '', FALSE);   +
 SET check_function_bodies = FALSE;                        +
 SET xmloption = content;                                  +
 SET client_min_messages = warning;                        +
 SET row_security = off;                                   +                                                      
 --                                                        +
 -- Name: v3; Type: VIEW; Schema: public; Owner: adbpgadmin+
 --                                                        +                                                       
 CREATE OR REPLACE VIEW public.v3 AS                       +
  SELECT test.a,                                           +
     v2.c                                                  +
    FROM (public.test                                      +
      JOIN public.v2 ON ((test.b = v2.b)));                +
                                                           +                                                       
 ALTER TABLE public.v3 OWNER TO adbpgadmin;                +                                                      
 --                                                        +
 -- Greenplum Database database dump complete              +
 --                                                        +                                                                                                    +
 --                                                        +
 -- Greenplum Database database dump                       +

 -- Dumped from database version 12.12                     +
 -- Dumped by pg_dump version 12.12                        +                                                       
 SET gp_default_storage_options = '';                      +
 SET statement_timeout = 0;                                +
 SET lock_timeout = 0;                                     +
 SET idle_in_transaction_session_timeout = 0;              +
 SET idle_session_timeout = 0;                             +
 SET client_encoding = 'UTF8';                             +
 SET standard_conforming_strings = on;                     +
 SELECT pg_catalog.set_config('search_path', '', false);   +
 SET check_function_bodies = false;                        +
 SET xmloption = content;                                  +
 SET client_min_messages = warning;                        +
 SET row_security = off;                                   +                                                       
 --                                                        +
 -- Name: v3; Type: VIEW; Schema: public; Owner: adbpgadmin+
 --                                                        +                                                           
 CREATE OR REPLACE VIEW public.v3 AS                       +
  SELECT test.a,                                           +
     v2.c                                                  +
    FROM (public.test                                      +
      JOIN public.v2 ON ((test.b = v2.b)));                +
                                                           +                                                          
 ALTER TABLE public.v3 OWNER TO adbpgadmin;                +                                                        
 --                                                        +
 -- Greenplum Database database dump complete              +
 --                                                        +                                                      
 (1 ROW)

執行drop_dependency_table函數將依賴視圖全部刪除，修改基表test的b列後，執行create_dependency_table函數進行依賴視圖的重建。

SELECT drop_dependency_table();
NOTICE: DROP cascades TO VIEW v3
NOTICE: VIEW "v3" does NOT exist, skipping
 drop_dependency_table 
-----------------------
(1 ROW)
SELECT create_dependency_table();
 create_dependency_table 
-------------------------
(1 ROW)