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 ...)，則會使用指定的結構描述建立檢視。否則，會使用目前的結構描述來建立。

檢視的名稱必須不同於相同結構描述中任何其他關係 (資料表、索引、檢視) 的名稱。

參數

備註

使用 DROP VIEW 陳述式捨棄檢視。

您應謹慎考慮檢視的資料欄名稱和資料類型。例如，CREATE VIEW vista AS SELECT 'Hello World'; 不建議使用，因為資料欄名稱預設為 ?column?;。此外，您可能不希望資料欄資料類型預設為 text。

更好的方法是明確指定資料欄名稱和資料類型，例如：CREATE VIEW vista AS SELECT text 'Hello World' AS hello;。

根據預設，檢視中參考的基礎關係存取權，取決於檢視擁有者的許可而定。在某些情況下，這可用來提供對基礎資料表的安全但受限制的存取。不過，並非所有檢視都安全，不會遭到竄改。

可更新的檢視

簡單檢視可自動更新：系統允許檢視使用 INSERT、UPDATE 和 DELETE 陳述式；方法與一般資料表相同。如果檢視符合下列所有條件，則可自動更新檢視：

可自動更新的檢視可能混合可更新和不可更新資料欄。如果資料欄是基礎關係可更新資料欄的簡單參考，則可更新資料欄。否則，資料欄為唯讀，一旦 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 查看關係層級套用哪些條件 (而不會鎖定資料列)，以及未套用哪些條件。

根據預設，不符合上述所有條件的更複雜檢視為唯讀：系統不允許在這類檢視上插入、更新或刪除。

範例

建立包含所有喜劇影片的檢視。

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。影片資料表中的所有資料欄將為可更新，而計算的資料欄 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 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 權限。這些限制會強制透過捨棄並重新建立檢視，確保即使變更擁有者也不會執行您無權執行的任何動作。)

參數

備註

基於 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 會捨棄現有的檢視。您必須是檢視的擁有者才能執行此命令。

參數

範例

DROP VIEW kinds;          

相容性

此命令符合 SQL 標準，但標準只允許每個命令捨棄一個檢視；IF EXISTS 選項除外，這是 Aurora DSQL 支援的 PostgreSQL 擴充功能。