Aurora DSQL 中支持的 SQL 命令子集 - Amazon Aurora DSQL
CREATE TABLEALTER TABLECREATE VIEWALTER VIEWDROP VIEW

Aurora DSQL 中支持的 SQL 命令子集

此 PostgreSQL 部分提供有关支持的表达式的详细信息，重点介绍具有大量参数集和子命令的命令。例如，PostgreSQL 中的 CREATE TABLE 提供了许多子句和参数。本节介绍 Aurora DSQL 对于这些命令支持的 PostgreSQL 语法元素。

CREATE TABLE

CREATE TABLE 定义一个新表。

CREATE TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression )|
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | ALL }

index_parameters in UNIQUE, and PRIMARY KEY constraints are:
[ INCLUDE ( column_name [, ... ] ) ]

ALTER TABLE

ALTER TABLE 更改表的定义。

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type 
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

CREATE VIEW

CREATE VIEW 定义新的持久视图。Aurora DSQL 不支持临时视图；仅支持持久视图。

支持的语法

CREATE [ OR REPLACE ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

描述

CREATE VIEW 定义查询的视图。此视图并未实际实现。相反，每次在查询中引用视图时，都会运行查询。

CREATE or REPLACE VIEW 与之类似，但如果同名的视图已经存在，则将替换该视图。新查询生成的列必须与现有视图查询生成的列相同（即顺序相同且数据类型相同的列名），但它可能会在列表末尾添加其它列。产生输出列的计算方法可能有所不同。

如果提供架构名称（例如 CREATE VIEW myschema.myview ...），则在指定的架构中创建视图。否则，将在当前架构中创建该视图。

视图的名称必须与同一架构中任何其它关系（表、索引、视图）的名称不同。

参数

CREATE VIEW 支持各种参数来控制可自动更新的视图的行为。

RECURSIVE

创建递归视图。语法：CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...; 等同于 CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;。

必须为递归视图指定视图列名列表。

name

要创建的视图的名称，可以选择使用架构进行限定。必须为递归视图指定列名列表。

column_name

要用于视图中各列的名称的可选列表。如果未提供，则从查询中推断出列名。

WITH ( view_option_name [= view_option_value] [, ... ] )

此子句为视图指定可选参数；支持以下参数。

  • check_option (enum)：此参数可以为 local 或 cascaded，等同于指定 WITH [ CASCADED | LOCAL ] CHECK OPTION。

  • security_barrier (boolean)：如果视图旨在提供行级安全性，则应使用此参数。Aurora DSQL 目前不支持行级安全性，但此选项仍会强制首先评估视图的 WHERE 条件（以及任何使用标记为 LEAKPROOF 的运算符的条件）。

  • security_invoker (boolean)：此选项会导致根据视图用户而不是视图所有者的权限来检查底层基本关系。有关完整详细信息，请参阅下面的备注。

可以使用 ALTER VIEW 在现有视图上更改上述所有选项。

query

SELECT 或 VALUES 命令，将提供视图的列和行。

  • WITH [ CASCADED | LOCAL ] CHECK OPTION：此选项控制可自动更新的视图的行为。指定此选项后，将检查视图上的 INSERT 和 UPDATE 命令，以确保新行满足视图定义条件（也就是说，检查新行以确保它们在视图中可见）。否则，将拒绝更新。如果未指定 CHECK OPTION，则支持视图上的 INSERT 和 UPDATE 命令创建在视图中不可见的行。支持以下检查选项。

  • LOCAL：仅根据在视图本身中直接定义的条件来检查新行。不检查在底层基本视图上定义的任何条件（除非它们也指定了 CHECK OPTION）。

  • CASCADED：根据视图和所有底层基本视图的条件检查新行。如果指定了 CHECK OPTION，但既未指定 LOCAL 也未指定 CASCADED，则假定为 CASCADED。

注意

CHECK OPTION 不得与 RECURSIVE 视图一起使用。仅在可自动更新的视图上才支持 CHECK OPTION。

备注

使用 DROP VIEW 语句可删除视图。

应仔细考虑视图列的名称和数据类型。例如，不建议使用 CREATE VIEW vista AS SELECT 'Hello World';，因为列名称默认为 ?column?;。此外，列的数据类型默认为 text，这可能不是您想要的。

更好的方法是显式指定列名和数据类型，例如：CREATE VIEW vista AS SELECT text 'Hello World' AS hello;。

默认情况下，对视图中引用的底层基本关系的访问权限由视图所有者的权限决定。在某些情况下，这可用于提供对底层表的安全但受限的访问。但是，并非所有视图都能防止篡改。

  • 如果视图的 security_invoker 属性设置为 true，则对底层基本关系的访问权限取决于执行查询的用户的权限，而不是视图所有者的权限。因此，安全调用程序视图的用户必须对该视图及其底层基本关系拥有相关权限。

  • 如果任何底层基本关系是安全调用程序视图，则会被视为直接从原始查询访问了该视图。因此，安全调用程序视图将始终使用当前用户的权限来检查其底层基本关系，即使从没有 security_invoker 属性的视图访问该视图也是如此。

  • 视图中调用的函数的处理方式与使用视图直接从查询中调用函数的处理方式相同。因此，视图的用户必须具有权限来调用该视图使用的所有函数。视图中的函数是以执行查询的用户或函数所有者的权限来执行的，具体取决于函数是定义为 SECURITY INVOKER 还是 SECURITY DEFINER。例如，直接在视图中调用 CURRENT_USER 将始终返回进行调用的用户，而不是视图所有者。这不受视图的 security_invoker 设置影响，因此将 security_invoker 设置为 false 的视图不等同于 SECURITY DEFINER 函数。

  • 创建或替换视图的用户必须对视图查询中引用的任何架构具有 USAGE 权限，才能在这些架构中查找引用的对象。但请注意，只有在创建或替换视图时才会进行这种查找。因此，即使对于安全调用程序视图，视图的用户也只需要对包含视图的架构拥有 USAGE 权限，而不需要对视图查询中引用的架构拥有此权限。

  • 在现有视图上使用 CREATE OR REPLACE VIEW 时，仅更改视图的定义 SELECT 规则以及任何 WITH ( ... )参数及其 CHECK OPTION。其它视图属性（包括所有权、权限和非 SELECT 规则）保持不变。您必须拥有视图才能替换它（这包括成为拥有角色的成员）。

可更新视图

简单视图可自动更新：系统将支持在视图上使用 INSERT、UPDATE 和 DELETE 语句，其方式与在常规表上相同。如果视图满足以下所有条件，则该视图可自动更新：

  • 视图在其 FROM 列表中必须确切只有一个条目，该条目必须是一个表或另一个可更新的视图。

  • 视图定义不得在顶层包含 WITH、DISTINCT、GROUP BY、HAVING、LIMIT 或 OFFSET 子句。

  • 视图定义不得在顶层包含集合操作（UNION、INTERSECT 或 EXCEPT）。

  • 视图的选择列表不得包含任何聚合、窗口函数或返回集合的函数。

可自动更新的视图可能包含可更新和不可更新的列的组合。如果列是对底层基本关系的可更新列的简单引用，则该列是可更新的。否则，该列是只读的，如果 INSERT 或 UPDATE 语句尝试为其赋值，则会发生错误。

对于可自动更新的视图，系统会将视图上的任何 INSERT、UPDATE 或 DELETE 语句转换为底层基本关系上的相应语句。完全支持带有 ON CONFLICT UPDATE 子句的 INSERT 语句。

如果可自动更新的视图包含 WHERE 条件，则该条件将限制基本关系的哪些行可供视图上的 UPDATE 和 DELETE 语句修改。但是，UPDATE 可以更改某行以使其不再满足 WHERE 条件，从而使其在视图中不可见。同样，INSERT 命令可能会插入不满足 WHERE 条件的基本关系行，从而使它们在视图中不可见。ON CONFLICT UPDATE 可能同样会影响视图中不可见的现有行。

可以使用 CHECK OPTION 来防止 INSERT 和 UPDATE 命令创建在视图中不可见的行。

如果使用 security_barrier 属性标记了可自动更新的视图，则该视图的所有 WHERE 条件（以及任何使用标记为 LEAKPROOF 的运算符的条件）始终在视图用户已添加的任何条件之前进行评估。请注意，由于这一原因，最终未返回的行（因为它们没有通过用户的 WHERE 条件）可能最终仍会被锁定。可以使用 EXPLAIN 来查看哪些条件应用于关系级别（因此不会锁定行），哪些不适用。

默认情况下，未满足所有这些条件的更复杂的视图是只读的：系统不支持在视图上执行插入、更新或删除操作。

注意

在视图上执行插入、更新或删除操作的用户必须对该视图具有相应的插入、更新或删除权限。默认情况下，视图的所有者必须对底层基本关系拥有相关的权限，而执行更新的用户不需要对底层基本关系拥有任何权限。但是，如果视图将 security_invoker 设置为 true，则执行更新的用户（而不是视图所有者）必须对底层基本关系拥有相关权限。

示例

创建由所有喜剧电影组成的视图。

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

这将创建一个视图，其中包含在创建视图时位于 film 表中的列。虽然使用 * 来创建视图，但之后添加到表中的列不会成为视图的一部分。

使用 LOCAL CHECK OPTION 创建视图。

CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG'
    WITH CASCADED CHECK OPTION;

这将创建一个同时检查新行的 kind 和 classification 的视图。

创建一个混合了可更新和不可更新的列的视图。

CREATE VIEW comedies AS
    SELECT f.*,
           country_code_to_name(f.country_code) AS country,
           (SELECT avg(r.rating)
            FROM user_ratings r
            WHERE r.film_id = f.id) AS avg_rating
    FROM films f
    WHERE f.kind = 'Comedy';

此视图将支持 INSERT、UPDATE 和 DELETE。films 表中的所有列都将是可更新的，而计算列 country 和 avg_rating 将是只读的。

CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;
注意

虽然递归视图的名称在此 CREATE 中是架构限定的，但其内部自引用不是架构限定的。这是因为隐式创建的公用表表达式（CTE）的名称不能由架构限定。

兼容性

CREATE OR REPLACE VIEW 是 PostgreSQL 语言扩展。WITH ( ... ) 子句也是扩展，安全屏障视图和安全调用程序视图也是如此。Aurora DSQL 支持这些语言扩展。

ALTER VIEW

ALTER VIEW 语句支持更改现有视图的各种属性，并且 Aurora DSQL 支持此命令的所有 PostgreSQL 语法。

支持的语法

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

描述

ALTER VIEW 更改视图的各种辅助属性。（如果要修改视图的定义查询，请使用 CREATE OR REPLACE VIEW。）您必须拥有该视图的所有权才能使用 ALTER VIEW。要更改视图的架构，您还必须对新架构具有 CREATE 权限。要更改所有者，您必须能够 SET ROLE 以使用新的拥有角色，并且该角色必须对视图的架构拥有 CREATE 权限。这些限制强制要求：更改所有者不会执行任何您无法通过删除并重新创建视图来完成的事情。

参数

ALTER VIEW 参数

name

现有视图的名称（可选择架构限定）。

column_name

现有列的新名称。

IF EXISTS

如果视图不存在，不引发错误。在这种情况下，将发出通知。

SET/DROP DEFAULT

这些表单为列设置或移除默认值。视图列的默认值会替换为任何以视图为目标的 INSERT 或 UPDATE 命令。视图的默认值将优先于基本关系中的任何默认值。

new_owner

视图的新所有者的用户名。

new_name

视图的新名称。

new_schema

视图的新架构。

SET ( view_option_name [= view_option_value] [, ... ] )
RESET ( view_option_name [, ... ] )

设置或重置视图选项。以下是支持的选项。

  • check_option (enum)：更改视图的检查选项。值必须为 local 或 cascaded。

  • security_barrier (boolean)：更改视图的 security-barrier 属性。该值必须是布尔值，例如 true 或 false。

  • security_invoker (boolean)：更改视图的 security-barrier 属性。该值必须是布尔值，例如 true 或 false。

备注

出于 PostgreSQL 的历史原因，ALTER TABLE 也可以与视图一起使用；但视图中支持的 ALTER TABLE 的仅有变体与前面所示的变体等效。

示例

将视图 foo 重命名为 bar。

ALTER VIEW foo RENAME TO bar;

将默认列值附加到可更新的视图。

CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1);  -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2);  -- ts will receive the current time
兼容性

ALTER VIEW 是 Aurora DSQL 支持的 SQL 标准的 PostgreSQL 扩展。

DROP VIEW

DROP VIEW 语句移除现有视图。Aurora DSQL 支持此命令的完整 PostgreSQL 语法。

支持的语法

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

描述

DROP VIEW 删除现有视图。要执行此命令，您必须是视图的所有者。

参数

IF EXISTS

如果视图不存在，不引发错误。在这种情况下，将发出通知。

name

要移除的视图的名称（可选择架构限定）。

CASCADE

自动删除依赖于视图的对象（如其它视图），进而删除依赖于这些对象的所有对象。

RESTRICT

如果任何对象依赖于该视图，则拒绝删除该视图。这是默认值。

示例

DROP VIEW kinds;

兼容性

此命令符合 SQL 标准，除了该标准只支持每个命令删除一个视图，并且 IF EXISTS 选项除外（这是 Aurora DSQL 支持的 PostgreSQL 扩展）。