9.4의 추가되는 기능에 대한 test tip입니다.
# view 생성 후 insert
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL,
quantity INT,
reserved INT DEFAULT 0);
CREATE VIEW products_view AS
SELECT product_id,
product_name,
quantity,
(quantity - reserved) AS available
FROM products
WHERE quantity IS NOT NULL;
#view에 대한 insert
INSERT INTO products_view (product_name, quantity) VALUES
('Budget laptop', 100),
('Premium laptop', 10);
king=# SELECT * FROM products;
product_id | product_name | quantity | reserved
------------+----------------+----------+----------
1 | Budget laptop | 100 | 0
2 | Premium laptop | 10 | 0
(2 rows)
king=# select * from products_view;
product_id | product_name | quantity | available
------------+----------------+----------+-----------
1 | Budget laptop | 100 | 100
2 | Premium laptop | 10 | 10
#view에 대한 update 확인
UPDATE products_view SET quantity = quantity - 10 WHERE product_id = 1;
UPDATE 1
# 비 갱신 컬럼에 대해서는 자동 업데이트를 방지
UPDATE products_view SET available = available - 10 WHERE product_id = 1;
ERROR: cannot update column "available" of view "products_view"
DETAIL: View columns that are not columns of their base relation are not updatable.
----9.3의 경우 view에 대한 insert&update가 되지 않음
king=# INSERT INTO products_view (product_name, quantity) VALUES
('Budget laptop', 100),
('Premium laptop', 10);
ERROR: cannot insert into view "products_view"
DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable.
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
king=# UPDATE products_view SET quantity = quantity - 10 WHERE product_id = 1;
ERROR: cannot update view "products_view"
DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable.
HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
