PostgreSQL 教程: 管理视图

九月 8, 2023

摘要:在本教程中,您将了解视图以及如何在 PostgreSQL 中管理视图。

PostgreSQL 视图简介

视图是存储的查询。视图可以作为 PostgreSQL 中的虚拟表进行访问。换句话说,PostgreSQL 视图是一种逻辑表,通过 SELECT 语句表示一个或多个基础表的数据。

请注意,除了物化视图之外,视图并不像表那样物理地存储数据。

postgresql view

视图在某些情况下非常有用,例如:

  • 视图有助于简化查询的复杂性,因为您可以使用简单的SELECT语句来查询基于复杂查询的视图。
  • 与表一样,您可以通过包含授权用户查看的特定数据的视图向用户授予权限
  • 即使基础表的列发生变化,视图也能提供一致的访问层。

创建 PostgreSQL 视图

为了创建视图,我们使用CREATE VIEW语句。CREATE VIEW语句最简单的语法如下:

CREATE VIEW view_name AS query;

首先,在CREATE VIEW子句后指定视图的名称,然后在AS关键字后放置查询。查询可以是简单SELECT语句,也可以是带有连接的复杂SELECT语句。

PostgreSQL CREATE VIEW 示例

例如,在我们的示例数据库中,我们有四个表:

  1. customer – 存储所有客户数据
  2. address – 存储客户的地址
  3. city – 存储城市数据
  4. country – 存储国家数据

posgresql view - tables

如果要获取完整的客户数据,通常可以构建如下的 join 语句

 SELECT cu.customer_id AS id,
    cu.first_name || ' ' || cu.last_name AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'
            ELSE ''
        END AS notes,
    cu.store_id AS sid
   FROM customer cu
     INNER JOIN address a USING (address_id)
     INNER JOIN city USING (city_id)
     INNER JOIN country USING (country_id);

查询结果如下图所示:

posgresql view - customers data

这个查询相当复杂。但是,您可以创建一个名为customer_master的视图,如下:

CREATE VIEW customer_master AS
  SELECT cu.customer_id AS id,
    cu.first_name || ' ' || cu.last_name AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'
            ELSE ''
        END AS notes,
    cu.store_id AS sid
   FROM customer cu
     INNER JOIN address a USING (address_id)
     INNER JOIN city USING (city_id)
     INNER JOIN country USING (country_id);

从现在开始,每当您需要获取完整的客户数据时,您只需执行以下简单SELECT语句从视图中查询即可:

SELECT
	*
FROM
	customer_master;

此查询生成的结果与使用上述联接的复杂查询相同。

更改 PostgreSQL 视图

要更改视图的定义查询,请使用带上OR REPLACE关键字的CREATE VIEW语句,如下所示:

CREATE OR REPLACE view_name 
AS 
query

PostgreSQL 不支持删除视图中的现有列,至少到版本 9.4 为止。如果您尝试这样做,您将收到一条错误消息:“[Err] 错误:无法从视图中删除列”。查询生成的列必须与创建视图时生成的列相同。更具体地说,新列必须具有与创建时相同的名称、相同的数据类型以及相同的顺序。但是,PostgreSQL 允许您在列列表的末尾添加附加列。

例如,您可以将电子邮件添加到customer_master视图,如下所示:

CREATE VIEW customer_master AS
  SELECT cu.customer_id AS id,
    cu.first_name || ' ' || cu.last_name AS name,
    a.address,
    a.postal_code AS "zip code",
    a.phone,
    city.city,
    country.country,
        CASE
            WHEN cu.activebool THEN 'active'
            ELSE ''
        END AS notes,
    cu.store_id AS sid,
    cu.email
   FROM customer cu
     INNER JOIN address a USING (address_id)
     INNER JOIN city USING (city_id)
     INNER JOIN country USING (country_id);

现在,如果您从customer_master视图中查询数据,您将在列表末尾看到email列。

SELECT
	*
FROM
	customer_master;

posgresql alter view - customers master

要更改视图的定义,可以使用ALTER VIEW语句。例如,您可以使用以下语句将视图名称从customer_master更改为customer_info

ALTER VIEW customer_master RENAME TO customer_info;

PostgreSQL 允许您设置列名的默认值、更改视图的模式、设置或重置视图的选项。有关更改视图定义的详细信息,请查看 PostgreSQL ALTER VIEW 语句

删除 PostgreSQL 视图

要删除 PostgreSQL 中的现有视图,请使用DROP VIEW语句,如下:

DROP VIEW [ IF EXISTS ] view_name;

您可以在DROP VIEW子句后指定要删除的视图的名称。删除数据库中不存在的视图将导致错误。为了避免这种情况,您通常会在语句中添加IF EXISTS选项来指示 PostgreSQL 删除视图(如果存在),如果不存在则不执行任何操作。

例如,要删除您创建的customer_info视图,请执行以下查询:

DROP VIEW IF EXISTS customer_info;

视图customer_info将从数据库中删除。

在本教程中,我们向您展示了如何创建、更改和删除 PostgreSQL 视图。