CREATE VIEW

자주 쓰는 복잡한 쿼리를 매번 다시 쓰는 대신, 이름을 붙여 재사용하고 싶을 때 뷰가 필요해요. CREATE VIEW는 쿼리의 뷰를 정의하는 명령이에요. 뷰는 물리적으로 구체화되지 않아요. 대신 쿼리에서 뷰가 참조될 때마다 그 쿼리가 실행돼요.

CREATE OR REPLACE VIEW는 비슷하지만, 같은 이름의 뷰가 이미 있으면 그것을 대체해요. 새 쿼리는 기존 뷰 쿼리가 만들었던 것과 같은 컬럼을 만들어야 해요(즉 같은 순서·같은 데이터 타입의 같은 컬럼 이름) 하지만 목록 끝에 컬럼을 추가할 수는 있어요. 출력 컬럼을 만들어 내는 계산은 완전히 달라질 수 있어요.

스키마 이름을 주면(예: CREATE VIEW myschema.myview ...) 지정된 스키마에 뷰가 만들어지고, 생략하면 현재 스키마에 만들어져요. 임시 뷰는 특별한 스키마에 존재하므로 임시 뷰를 만들 때는 스키마 이름을 줄 수 없어요. 뷰 이름은 같은 스키마의 다른 릴레이션(테이블·시퀀스·인덱스·뷰·구체화 뷰·외부 테이블)의 이름과 달라야 해요.

출처: PostgreSQL 문서

본문

Synopsis

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

Description

CREATE VIEW는 쿼리의 뷰를 정의해요. 뷰는 물리적으로 구체화되지 않아요. 대신 쿼리에서 뷰가 참조될 때마다 그 쿼리가 실행돼요.

CREATE OR REPLACE VIEW는 비슷하지만, 같은 이름의 뷰가 이미 있으면 그것을 대체해요. 새 쿼리는 기존 뷰 쿼리가 생성한 것과 같은 컬럼을 만들어야 해요(즉 같은 순서·같은 데이터 타입의 같은 컬럼 이름). 다만 목록 끝에 추가 컬럼을 더할 수는 있어요. 출력 컬럼을 만들어 내는 계산은 완전히 달라질 수 있어요.

스키마 이름을 주면(예: CREATE VIEW myschema.myview ...) 뷰가 지정된 스키마에 만들어져요. 그렇지 않으면 현재 스키마에 만들어져요. 임시 뷰는 특별한 스키마에 존재하므로, 임시 뷰를 만들 때는 스키마 이름을 줄 수 없어요. 뷰 이름은 같은 스키마의 다른 릴레이션(테이블·시퀀스·인덱스·뷰·구체화 뷰·외부 테이블)의 이름과 달라야 해요.

Parameters

TEMPORARY 또는 TEMP

지정하면 뷰가 임시 뷰로 만들어져요. 임시 뷰는 현재 세션이 끝날 때 자동으로 삭제돼요. 임시 뷰가 존재하는 동안 같은 이름의 기존 영구 릴레이션은, 스키마 한정 이름으로 참조되지 않는 한 현재 세션에 보이지 않아요.

뷰가 참조하는 테이블 중 하나라도 임시면, TEMPORARY를 지정했는지와 무관하게 뷰가 임시 뷰로 만들어져요.

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)

뷰가 행 수준 보안을 제공하려는 것이라면 이걸 써야 해요. 전체 내용은 39.5절을 참고하세요.

security_invoker (boolean)

이 옵션은 밑에 있는 기본 릴레이션들을 뷰 소유자의 권한이 아니라 뷰 사용자의 권한으로 검사하게 해요. 전체 내용은 아래 Notes를 참고하세요.

위 옵션들은 모두 ALTER VIEW로 기존 뷰에서 바꿀 수 있어요.

query

뷰의 컬럼·행을 제공할 SELECT나 VALUES 명령이에요.

WITH [ CASCADED | LOCAL ] CHECK OPTION

이 옵션은 자동으로 갱신 가능한 뷰의 동작을 제어해요. 이 옵션을 지정하면 뷰의 INSERT, UPDATE, MERGE 명령이 검사되어 새 행이 뷰 정의 조건을 만족하는지 확인해요(즉 새 행이 뷰를 통해 보이는지 검사돼요). 그렇지 않으면 갱신이 거부돼요. CHECK OPTION을 지정하지 않으면 뷰의 INSERT, UPDATE, MERGE 명령이 뷰를 통해 보이지 않는 행을 만들 수 있어요. 다음 체크 옵션이 지원돼요.

LOCAL

새 행은 뷰 자체에 직접 정의된 조건에 대해서만 검사돼요. 밑에 있는 기본 뷰에 정의된 조건은 검사되지 않아요(CHECK OPTION을 지정한 경우가 아니면).

CASCADED

새 행은 뷰와 모든 밑에 있는 기본 뷰의 조건에 대해 검사돼요. CHECK OPTION을 지정했는데 LOCAL도 CASCADED도 지정하지 않으면 CASCADED가 가정돼요.

CHECK OPTION은 RECURSIVE 뷰와 함께 쓸 수 없어요.

CHECK OPTION은 자동으로 갱신 가능하고 INSTEAD OF 트리거나 INSTEAD 규칙이 없는 뷰에서만 지원된다는 점을 유의하세요. 자동으로 갱신 가능한 뷰가 INSTEAD OF 트리거가 있는 기본 뷰 위에 정의되면, LOCAL CHECK OPTION으로 자동 갱신 가능한 뷰의 조건을 검사할 수 있지만, INSTEAD OF 트리거가 있는 기본 뷰의 조건은 검사되지 않아요(계단식 체크 옵션은 트리거로 갱신되는 뷰로 계단식 내려가지 않고, 트리거로 갱신되는 뷰에 직접 정의된 어떤 체크 옵션도 무시돼요). 뷰나 그 기본 릴레이션 중 하나에 INSERT·UPDATE 명령을 다시 쓰게 하는 INSTEAD 규칙이 있으면, 다시 쓰여진 쿼리에서 모든 체크 옵션이 무시돼요(INSTEAD 규칙이 있는 릴레이션 위에 정의된 자동 갱신 가능한 뷰의 검사도 포함해요). MERGE는 뷰나 그 기본 릴레이션 중 하나에 규칙이 있으면 지원되지 않아요.

Notes

뷰를 삭제하려면 DROP VIEW 문을 사용해요.

뷰의 컬럼 이름·타입이 원하는 대로 배정되도록 주의하세요. 예를 들어

CREATE VIEW vista AS SELECT 'Hello World';

이것은 안 좋은 형태예요. 컬럼 이름이 ?column?로 기본 설정되고, 데이터 타입도 text로 기본 설정되는데 원하는 게 아닐 수 있기 때문이죠. 뷰 결과의 문자열 리터럴에 대한 더 나은 스타일은 이런 것입니다.

CREATE VIEW vista AS SELECT text 'Hello World' AS hello;

기본적으로 뷰에서 참조되는 기본 릴레이션에 대한 접근은 뷰 소유자의 권한으로 결정돼요. 어떤 경우에는 이것으로 밑에 있는 테이블에 안전하지만 제한된 접근을 제공할 수 있어요. 하지만 모든 뷰가 변조에 안전한 것은 아니에요. 39.5절을 참고하세요.

뷰에 security_invoker 속성이 true로 설정되어 있으면, 기본 릴레이션에 대한 접근이 뷰 소유자 대신 쿼리를 실행하는 사용자의 권한으로 결정돼요. 그래서 security invoker 뷰의 사용자는 뷰와 그 기본 릴레이션에 관련 권한이 있어야 해요.

밑에 있는 기본 릴레이션 중 하나가 security invoker 뷰라면, 원래 쿼리에서 직접 접근한 것처럼 취급돼요. 그래서 security invoker 뷰는 security_invoker 속성이 없는 뷰에서 접근되더라도 항상 현재 사용자의 권한으로 기본 릴레이션을 검사해요.

밑에 있는 기본 릴레이션 중 하나에 행 수준 보안이 활성화되어 있으면, 기본적으로 뷰 소유자의 행 수준 보안 정책이 적용되고, 그 정책이 참조하는 추가 릴레이션에 대한 접근은 뷰 소유자의 권한으로 결정돼요. 다만 뷰에 security_invoker가 true로 설정되어 있으면, 기본 릴레이션이 뷰를 쓰는 쿼리에서 직접 참조된 것처럼 호출 사용자의 정책·권한이 대신 사용돼요.

뷰에서 호출된 함수는 뷰를 쓰는 쿼리에서 직접 호출된 것처럼 취급돼요. 그래서 뷰 사용자는 뷰가 쓰는 모든 함수를 호출할 권한이 있어야 해요. 뷰의 함수는 함수가 SECURITY INVOKER인지 SECURITY DEFINER인지에 따라 쿼리를 실행하는 사용자나 함수 소유자의 권한으로 실행돼요. 그래서 예를 들어 뷰에서 CURRENT_USER를 직접 호출하면 항상 뷰 소유자가 아니라 호출 사용자를 반환해요. 이는 뷰의 security_invoker 설정의 영향을 받지 않으므로, security_invoker가 false로 설정된 뷰는 SECURITY DEFINER 함수와 동등하지 않고 그 개념들을 혼동해서는 안 돼요.

뷰를 만들거나 대체하는 사용자는 뷰 쿼리에서 참조하는 스키마의 객체를 찾기 위해, 그 스키마에 대한 USAGE 권한이 있어야 해요. 다만 이 조회는 뷰를 만들거나 대체할 때만 일어난다는 점을 유의하세요. 따라서 뷰 사용자는 security invoker 뷰에서조차, 뷰 쿼리에서 참조하는 스키마가 아니라 뷰를 포함하는 스키마에 대한 USAGE 권한만 필요해요.

CREATE OR REPLACE VIEW를 기존 뷰에 쓰면, 뷰의 정의 SELECT 규칙과 WITH ( ... ) 매개변수, CHECK OPTION만 바뀌어요. 소유권·권한·비-SELECT 규칙을 포함한 다른 뷰 속성은 바뀌지 않아요. 뷰를 대체하려면 그 뷰를 소유해야 해요(소유 역할의 구성원인 것을 포함해요).

Updatable Views

단순한 뷰는 자동으로 갱신 가능해요. 시스템이 일반 테이블과 같은 방식으로 뷰에 INSERT, UPDATE, DELETE, MERGE 문을 쓸 수 있게 해 주죠. 뷰가 다음 조건을 모두 만족하면 자동으로 갱신 가능해요.

뷰의 FROM 목록에 정확히 하나의 항목이 있어야 하고, 그것은 테이블이거나 다른 갱신 가능한 뷰여야 해요.
뷰 정의가 최상위에 WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET 절을 포함하지 않아야 해요.
뷰 정의가 최상위에 집합 연산(UNION, INTERSECT, EXCEPT)을 포함하지 않아야 해요.
뷰의 선택 목록에 집계·창 함수·집합 반환 함수가 없어야 해요.

자동으로 갱신 가능한 뷰는 갱신 가능 컬럼과 갱신 불가 컬럼을 섞어 가질 수 있어요. 컬럼이 밑에 있는 기본 릴레이션의 갱신 가능 컬럼에 대한 단순 참조이면 갱신 가능하고, 그렇지 않으면 읽기 전용이며 INSERT, UPDATE, MERGE 문이 그 컬럼에 값을 할당하려 하면 오류가 나요.

뷰가 자동으로 갱신 가능하면 시스템은 뷰의 INSERT, UPDATE, DELETE, MERGE 문을 밑에 있는 기본 릴레이션의 해당 문으로 변환해요. ON CONFLICT UPDATE 절이 있는 INSERT 문은 완전히 지원돼요.

자동으로 갱신 가능한 뷰가 WHERE 조건을 포함하면, 그 조건은 뷰의 UPDATE, DELETE, MERGE 문으로 수정할 수 있는 기본 릴레이션의 행을 제한해요. 다만 UPDATE·MERGE는 행이 더 이상 WHERE 조건을 만족하지 않게(그래서 뷰를 통해 더 이상 보이지 않게) 바꾸는 것을 허용해요. 비슷하게 INSERT·MERGE 명령은 WHERE 조건을 만족하지 않아 뷰를 통해 보이지 않는 기본 릴레이션 행을 삽입할 수 있어요(ON CONFLICT UPDATE도 뷰를 통해 보이지 않는 기존 행에 비슷하게 영향을 줄 수 있어요). CHECK OPTION으로 INSERT, UPDATE, MERGE 명령이 뷰를 통해 보이지 않는 그런 행을 만들지 못하게 할 수 있어요.

자동으로 갱신 가능한 뷰가 security_barrier 속성으로 표시되면, 뷰의 모든 WHERE 조건(그리고 LEAKPROOF로 표시된 연산자를 쓰는 조건)이 뷰 사용자가 추가한 조건보다 항상 먼저 평가돼요. 전체 내용은 39.5절을 참고하세요. 이 때문에 결국 반환되지 않는 행(사용자의 WHERE 조건을 통과하지 못해서)이 잠길 수도 있다는 점을 유의하세요. EXPLAIN으로 어떤 조건이 릴레이션 수준에서 적용되는지(그래서 행을 잠그지 않는지) 확인할 수 있어요.

이 조건을 모두 만족하지 않는 더 복잡한 뷰는 기본적으로 읽기 전용이에요. 시스템이 뷰에 INSERT, UPDATE, DELETE, MERGE를 허용하지 않죠. 뷰에 INSTEAD OF 트리거를 만들어 갱신 가능한 뷰의 효과를 얻을 수 있어요. 이 트리거는 시도된 삽입 등을 다른 테이블의 적절한 작업으로 변환해야 해요. 자세한 내용은 CREATE TRIGGER를 참고하세요. 또 다른 방법은 규칙을 만드는 것(CREATE RULE 참고)이지만, 실제로는 트리거가 이해하고 올바르게 쓰기 더 쉬워요. 또한 MERGE는 규칙이 있는 릴레이션에서 지원되지 않는다는 점을 유의하세요.

뷰에 삽입·갱신·삭제를 수행하는 사용자는 뷰에 해당 삽입·갱신·삭제 권한이 있어야 해요. 또한 기본적으로 갱신을 수행하는 사용자는 기본 릴레이션에 대한 권한이 필요 없는 반면, 뷰의 소유자는 기본 릴레이션에 대한 관련 권한이 있어야 해요(39.5절 참고). 다만 뷰에 security_invoker가 true로 설정되어 있으면, 갱신을 수행하는 사용자(뷰 소유자가 아니라)가 기본 릴레이션에 대한 관련 권한이 있어야 해요.

Examples

모든 코미디 영화로 구성된 뷰를 만들어 볼게요.

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

이것은 뷰를 만들 때 film 테이블에 있는 컬럼들을 담은 뷰를 만들어요. *를 써서 뷰를 만들었지만, 나중에 테이블에 추가된 컬럼은 뷰의 일부가 되지 않아요.

LOCAL CHECK OPTION이 있는 뷰를 만들어 볼게요.

CREATE VIEW universal_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'U'
    WITH LOCAL CHECK OPTION;

이것은 comedies 뷰를 기반으로, kind = 'Comedy'와 classification = 'U'인 영화만 보여 주는 뷰를 만들어요. 새 행에 classification = 'U'가 없으면 뷰에서 INSERT·UPDATE하려는 시도가 모두 거부되지만, 영화 kind는 검사되지 않아요.

CASCADED 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은 읽기 전용이에요.

1부터 100까지의 숫자로 구성된 재귀 뷰를 만들어 볼게요.

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의 이름은 스키마 한정할 수 없기 때문이에요.

Compatibility

CREATE OR REPLACE VIEW는 PostgreSQL 언어 확장이에요. 임시 뷰의 개념도 마찬가지예요. WITH ( ... ) 절도 확장이고, security barrier 뷰와 security invoker 뷰도 확장이에요.

더 알아보기 (Learn more)

ALTER VIEW — 뷰의 정의·속성을 바꿀 때 써요.
DROP VIEW — 뷰를 삭제할 때 써요.
CREATE MATERIALIZED VIEW — 결과를 물리적으로 저장하는 구체화 뷰를 만들 때 써요.
CREATE TRIGGER — INSTEAD OF 트리거로 복잡한 뷰를 갱신 가능하게 만들 때 써요.
View Security — 뷰 보안을 다루는 39.5절이에요.