Giới thiệu Postgre View

POSTGRES VIEW

1. Postgres view là gì và tại sao lại dùng?

Views về cơ bản giống như một bảng ảo tạo nên từ nhiều bảng base khác nhau. Nói cách khác, nó thực chất là một câu query được tạo và đặt tên sẵn để kết hợp nhiều bảng lại, mà sau này ta chỉ cần gọi ra “tên” của view là sẽ lấy ra được kết quả kết hợp nhiều bảng đó mà không cần viết lại query.

Hãy thử tưởng tượng ta đang có bảng `roles` chứa danh sách các role trong hệ thống, `permissions` chứa các quyền của role và `roles_permissions` là bảng trung gian cho liên kết Many – Many giữa 2 bảng `roles` và `permissions` như dưới đây:

Mục đích của cụm ba bảng này sẽ dùng để check quyền mỗi request mà user bất kì gửi lên => nói cách khác với mọi request ta sẽ phải viết lệnh join 3 bảng để lấy ra tất cả quyền của một role nào đó => quá phức tạp.

Ngay lúc này ta có thể ứng dụng tính năng Views của Postgres, bằng cách tạo một View tên là “role_permission_view” định nghĩa sẵn bảng được join bởi 3 bảng này:

CREATE VIEW role_permission_view AS
SELECT
r.id AS role_id,
r.name AS role_name,
p.code AS permission_code,
p.description AS permission_description
FROM
permissions p,
roles_permissions rp,
roles r
WHERE
p.id = rp.permission_id AND r.id = rp.role_id;

Có thể thấy đây hoàn toàn là một câu query join 3 bảng như bình thường, khác cái là nó sẽ được gán vào một view tên là “role_permission_view” để sau này lấy ra sử dụng như thế này:

SELECT * FROM role_permission_view;

Có một vài điểm cần lưu ý:

+ Có một vài Views không thể lưu dữ liệu, ví dụ câu query dưới đây sẽ báo lỗi:

Đó là vì nếu muốn một view có thể update, insert, delete thì phải thỏa một số điều kiện nhất định, sẽ nói tới ở phần 3.

Với những view không thể lưu dữ liệu như trên, ta phải viết trigger cho sự kiện INSERT để quy định postgres sẽ làm gì khi có người insert data vào view.

Trên đây chỉ là một ví dụ đơn giản của trường hợp có thể sử dụng Views. Trên thực tế, có các câu query phức tạp, thì lúc đó mới thấy được lợi ích thực sự của việc sử dụng Views.

Ngoài việc sử dụng để định nghĩa sẵn các query phức tạp, views còn ứng dụng khi:

+ Muốn che giấu các trường nhạy cảm, ví dụ như password

+ Tạo một lớp abstrach, tương tự như tạo một Interface vậy, để trừu tượng hóa các base table bên dưới => Mang lại lợi ích tương tự như sử dụng Interface trong coding, giúp ta có thể thay đổi cấu trúc của các base table bên dưới mà không lo các thứ khác phụ thuộc vào nó có thể thay đổi theo.

1. Cách sử dụng Postgres view cơ bản

Để tạo một views mới, ta dùng syntax “CREATE VIEW”:

CREATE VIEW view_name

AS

query;

Trong đó:

+ view_name: tên của view đó

+ query: là một câu query SELECT dùng để định nghĩa view đó

Ví dụ ta có một table customer như sau:

Ta có thể tạo một view ‘contact’ để lấy ra các dữ liệu về thông tin liên lạc của customer:

CREATE VIEW contact AS

SELECT

first_name,

last_name,

email

FROM

customer;

Thử xem dữ liệu bên trong:

SELECT * FROM contact;

Như đã ví dụ ở phần đầu của bài viết, ta có thể định nghĩa query join nhiều bảng lại với nhau để tạo thành một view, ví dụ:

CREATE VIEW customer_info AS

SELECT

first_name,

last_name,

email,

phone,

city,

postal_code,

country

FROM

customer

INNER JOIN address USING (address_id)

INNER JOIN city USING (city_id)

INNER JOIN country USING (country_id);

Views này sẽ lấy ra dữ liệu từ 3 bảng address, city, country:

SELECT * FROM customer_info;

Tạo một view từ một view khác

CREATE VIEW customer_usa

AS

SELECT

\*

FROM

customer_info

WHERE

country = 'United States';

Câu trên sẽ tạo một view dựa vào view `customer_info` ban này, khác ở chỗ là sẽ chỉ lấy ra các customer ở ‘United States’:

SELECT * FROM customer_usa;

Sửa định nghĩa của views

Có thể sửa lại câu quey định nghĩa của một view bằng cách dùng CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW contact AS

SELECT

first_name,

last_name,

email,

phone

FROM

customer

INNER JOIN address USING (address_id);

Cú pháp tương tự như CREATE VIEW ban nãy, lúc này postgres sẽ tự động replace câu query của view cũ nếu như đã có view này tồn tại rồi.

SELECT * FROM contact;

Ta đã thấy sự thay đổi, có thêm cột `phone`.

DROP VIEW

Tất nhiên cũng có thể xóa đi một Views bằng cách dùng DROP VIEW:

DROP VIEW contact

1. Updatable views

Ở ví dụ phần 1, ta có thể thấy không thể INSERT dữ liệu vào một view lấy dữ liệu từ 2 bảng trở lên, vì nó phải thỏa một số điều kiện:

+ Trong câu query, sau FROM chỉ được có 1 table

+ Không được chứa những thứ sau ở top level:

• GROUP BY
• HAVING
• LIMIT
• OFFSET FETCH
• DISTINCT
• WITH
• UNION
• INTERSECT
• EXCEPT

+ List dữ liệu được select không được chứa các function:

• Window functions
• Set-returning function
• Aggregate functions

Nếu thỏa các điều kiện trên, ta gọi view đó là updatable view, tức là có thể insert, update, delete vào đó được.

Với base table là cities:

CREATE TABLE cities (
id SERIAL PRIMARY KEY ,
name VARCHAR(255),
population INT,
country VARCHAR(50)
);

Ví dụ view sau sẽ thỏa updatable view:

CREATE VIEW city_us

AS

SELECT

*

FROM

cities

WHERE

country = 'US';

Thử insert:

INSERT INTO city_us(name, population, country)
VALUES ('San Jose', 983459, 'US');

SELECT * FROM city_us;

Thử update:

UPDATE city_us
SET population = 1000000
WHERE name = 'San Jose';
SELECT * FROM city_us;

Thử delete:

DELETE FROM city_us
WHERE id = 1;
SELECT * FROM city_us;

1. Materialized views

Như đã đề cập ở đầu, views thực chất là một bảng ảo tạo nên từ các bảng thực, và nếu muốn update data của views thì view đó phải thỏa rất nhiều điện kiện.

Về bản chất thì view không chứa dữ liệu, ví dụ nếu có thể insert, thì nó sẽ insert vào base table.

Ngược lại, với Materialized views, thì views này có thể chứa dữ liệu vào chính nó, tức là ta sẽ có một bản clone các record đúng nghĩa từ các base table.

Vậy thực chất lúc này ý tưởng giống như ta đang sử dụng cache để clone ra các data hay dùng từ các table gốc vậy. Và cũng giống như khi cache, nó được dùng trong các truờng hợp cần fast data access.

CREATE MATERIALIZED VIEW \[IF NOT EXISTS\] view_name

AS

query

WITH \[NO\] DATA;

+ view_name: tên view

+ query: câu query định nghĩa

+ WITH [NO] DATA: nếu để WITH DATA, thì dữ liệu sẽ được load từ các base view vào view ngay khi view được tạo, còn nếu để WITH NO DATA thì view đó sẽ không thể lấy dữ liệu ra được, cho đến khi ta chủ động load data vào nó.

Lúc này sẽ báo lỗi, vì lúc đầu ta đã định nghĩa WITH NO DATA, nên dữ liệu chưa được load vào, do đó ta phải load bằng lệnh sau:

REFRESH MATERIALIZED VIEW rental_by_category;

Kết quả: