설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 59건, 최근 0 건
   

9.4beta Updatable view improvements

글쓴이 : 주킹 날짜 : 2014-11-18 (화) 15:41 조회 : 1909
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.
 
 

   

postgresdba.com