AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据仓库AnalyticDB 云原生数据仓库AnalyticDB PostgreSQL版 实践教程 依赖视图DDL修改最佳实践

依赖视图DDL修改最佳实践

更新时间：
产品详情
相关技术圈
我的收藏

本文介绍了在云原生数据仓库 AnalyticDB PostgreSQL 版中，修改被视图依赖的基表和视图时面临的限制，分析此类修改对依赖视图产生的影响，以及如何快速且安全地修改某个基表结构或视图。

注意事项

  • 本功能仅支持AnalyticDB PostgreSQL 7.0版实例。

  • 本文涉及的视图均为依赖于基表的依赖视图。

查看基表和视图的依赖关系

若您要查看基表和视图的依赖关系，需要创建查询依赖信息的函数，以如下图所示的基表和视图为例，为您介绍如何查看基表和视图的依赖关系。

image

  1. 使用如下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;

  2. 使用如下所示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

    被依赖表或依赖视图的名称。

  3. 使用如下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

有无影响

备注

RENAME

无影响

查询依赖视图的定义时，被依赖的基表或中间视图的重命名会随之更新。

RENAME COLUMN

无影响

查询依赖视图定义时，对应的SELECT列更新，但对应视图本身的列名不会修改。

RENAME CONSTRAINT

无影响

重命名约束。

SET SCHEMA

无影响

查询依赖视图的定义时，会自动更新SCHEMA信息。

ATTACH PARTITION

无影响

无

DETACH PARTITION

无影响

无

DROP COLUMN 引用列

有影响

级联删除直接删除视图。

image.png

ALTER COLUMN TYPE

有影响

语法级别限制。

image.png

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新增视图的追加列，追加列对已有列顺序没有影响，依赖的视图仍旧可以通过系统表信息找到对应的列，因此该操作可正常执行。

  • 禁止删除视图尾列。

    视图禁止删除尾列。

  • 禁止修改视图某一列的数据类型。

    视图不允许修改列字段名。

更多视图修改限制，请参见下表。

结构变更

有无影响

备注

新增某一中间列

有影响

直接修改中间视图定义来新增列，且有视图依赖该中间视图时会被禁止。

新增追加列

无影响

无

删除列

有影响

直接修改中间视图定义来删除列，且有视图依赖该中间视图时会被禁止。

修改引用列数据类型

有影响

没有被引用的字段没有影响。

修改字段名

有影响

中间视图不允许修改列字段名。

修改基表或视图

修改步骤

  1. 导出视图相关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中，断连或切换数据库都将消失。

  2. 执行DROP命令。

    使用SELECT drop_dependency_table()来执行DROP DDL。若DROP失败，可尝试查看临时表temp_drop_dependency_ddl并手动执行。

  3. 执行基表类型修改或新增列修改。

  4. 执行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信息并手动执行。

使用示例

以如下图所示的基表和视图为例，为您举例如何安全修改基表和视图。

image

  1. 执行如下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)

  2. 创建修改基表或视图的函数，该函数在修改基表或视图之前会删除所有依赖视图并记录依赖视图的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基础上进行手动修改与重建。

  3. 执行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)

  4. 执行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)
上一篇：使用DMS进行数据归档下一篇：漏斗分析函数与留存函数
文档推荐