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 ...), 뷰는 지정된 스키마에서 생성됩니다. 그렇지 않으면, 뷰가 현재 스키마에서 생성됩니다.

뷰의 이름은 동일한 스키마에 있는 다른 관계(테이블, 인덱스, 뷰)의 이름과 달라야 합니다.

파라미터

Notes

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 권한이 있어야 합니다. 이러한 제한은 소유자를 변경해도 뷰를 삭제하고 다시 생성함으로써 수행할 수 없는 작업은 수행하지 않도록 강제합니다.

파라미터

Notes

과거 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 표준을 준수합니다. 단, Aurora DSQL이 지원하는 PostgreSQL 확장인 IF EXISTS 옵션을 제외하고 표준에서 명령당 하나의 뷰만 삭제할 수 있습니다.