PostgreSQL 5 — PL/pgSQL: hàm, thủ tục & trigger

Cho tới giờ trong loạt bài, chúng ta xem PostgreSQL như một cái kho: bạn gửi câu SQL vào, nó trả dữ liệu ra. Nhưng Postgres còn là một môi trường lập trình đầy đủ. Bạn có thể viết hàm, thủ tục, vòng lặp, bắt ngoại lệ — và để chúng chạy ngay bên trong tiến trình của cơ sở dữ liệu, sát cạnh dữ liệu. Đó là lập trình phía server.

Bài này đi qua PL/pgSQL — ngôn ngữ thủ tục mặc định của Postgres — rồi tới function, procedure, trigger, và (quan trọng không kém) khi nào bạn nên và không nên dùng chúng. Vì mọi lệnh trong bài đều là DDL (CREATE FUNCTION, CREATE TRIGGER) hoặc CALL/DO — tức không phải SELECT — nên tất cả ví dụ chỉ để minh hoạ, không chạy trong sandbox. Hãy đọc chúng như bản thiết kế, và tự thử trên một instance Postgres thật (16/17+).

Vì sao lại đặt logic phía server?

Trước khi học cú pháp, cần hiểu vì sao người ta lại đưa logic vào tận trong CSDL, khi mà tầng ứng dụng (Java, Python, Go...) đã có sẵn cả một hệ sinh thái.

Lợi ích:

• Gần dữ liệu → ít round-trip. Một tác vụ cần đọc 10.000 dòng, tính toán, rồi ghi lại. Làm ở tầng app nghĩa là kéo 10.000 dòng qua mạng, xử lý, đẩy ngược lại — mười nghìn lượt đi về. Làm bằng một function trong DB thì dữ liệu không rời khỏi server. Với khối lượng lớn, khác biệt là hàng chục lần.
• Toàn vẹn tập trung. Nếu quy tắc "số dư không được âm" nằm trong một trigger, thì mọi đường ghi vào bảng đều tuân thủ — dù request đến từ web, từ job batch, hay từ một DBA gõ tay. Đặt quy tắc ở tầng app thì mỗi service phải tự nhớ áp dụng.
• Tái sử dụng. Một function tinh_lai_suat() gọi được từ SQL, từ trigger, từ nhiều ứng dụng khác nhau — viết một lần, dùng khắp nơi.

Đánh đổi (đừng bỏ qua):

• Khó version & test. Code trong DB không nằm gọn trong Git như code ứng dụng; phải có kỷ luật migration (Flyway, Liquibase, sqitch) mới quản được. Unit test cũng khó hơn: cần một DB thật để chạy.
• Logic bị phân tán. Khi nghiệp vụ nằm nửa ở app, nửa ở DB, người mới đọc code app sẽ không hiểu vì sao dữ liệu "tự" thay đổi — trigger chạy âm thầm sau lưng.
• Khoá vendor (vendor lock-in). PL/pgSQL không tương thích với MySQL, SQL Server hay Oracle. Đổ nhiều nghiệp vụ vào đây nghĩa là gắn chặt với Postgres.

Quan điểm cân bằng: hãy đặt vào DB những thứ bản chất là ràng buộc dữ liệu (toàn vẹn, audit, cột dẫn xuất đơn giản) và những tác vụ nặng theo tập (set-based) mà round-trip là nút cổ chai. Ngược lại, nghiệp vụ hay đổi, quy trình phức tạp, tích hợp bên ngoài nên ở tầng app — nơi dễ test, dễ version, dễ đọc. "Tất cả trong DB" và "không gì trong DB" đều là cực đoan.

PL/pgSQL: cấu trúc cơ bản

PL/pgSQL bọc SQL trong một ngôn ngữ có biến và cấu trúc điều khiển. Một khối luôn có dạng DECLARE / BEGIN / END, và thân function là một chuỗi ký tự (thường dùng dollar-quoting $$ ... $$ để khỏi phải escape dấu nháy).

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE FUNCTION nhan_doi(x integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    ket_qua integer;          -- khai báo biến
BEGIN
    ket_qua := x * 2;         -- gán bằng :=
    RETURN ket_qua;
END;
$$;

Biến và kiểu bám theo bảng. Bạn không cần gõ cứng kiểu — dùng %TYPE để "mượn" kiểu của một cột, và %ROWTYPE (hoặc tên bảng) để mượn cả một dòng. Khi cột đổi kiểu, function tự bám theo:

-- (minh hoạ, không chạy trong sandbox)
DECLARE
    v_so_du   accounts.balance%TYPE;   -- cùng kiểu với cột balance
    v_tk      accounts%ROWTYPE;        -- cả một dòng của accounts
    v_ten     text;

Tham số IN/OUT. Mặc định tham số là IN. Dùng OUT để trả nhiều giá trị mà không cần kiểu composite riêng, INOUT cho vừa nhận vừa trả:

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE FUNCTION chia(a numeric, b numeric,
                                OUT thuong numeric, OUT du numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    thuong := trunc(a / b);
    du     := a - thuong * b;
END;
$$;
-- gọi: SELECT * FROM chia(17, 5);  -> thuong=3, du=2

Kiểu trả về. Function có thể trả về:

• scalar — một giá trị (RETURNS integer, RETURNS text).
• RECORD / composite — một dòng gồm nhiều cột.
• SETOF / TABLE — nhiều dòng, dùng được ngay trong FROM như một bảng: RETURNS TABLE(id int, ten text).

Điều khiển luồng

Đây là phần khiến PL/pgSQL "giống ngôn ngữ lập trình". Các cấu trúc chính:

Rẽ nhánh: IF ... THEN ... ELSIF ... ELSE ... END IF; và CASE (dạng biểu thức hoặc dạng tìm điều kiện).

Vòng lặp:

• LOOP ... END LOOP; — vòng vô hạn, thoát bằng EXIT WHEN.
• WHILE dieu_kien LOOP ... END LOOP;
• FOR i IN 1..10 LOOP ... — lặp theo dãy số.
• FOR r IN SELECT ... LOOP ... — lặp trên từng dòng của một truy vấn (rất hay dùng).
• FOREACH x IN ARRAY mang LOOP ... — duyệt phần tử mảng.

Trả nhiều dòng từ function SETOF/TABLE:

• RETURN NEXT — thêm một dòng vào tập kết quả rồi tiếp tục.
• RETURN QUERY — đổ toàn bộ kết quả một SELECT vào tập trả về (nhanh và gọn hơn lặp thủ công).

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE FUNCTION tk_theo_kh(p_customer_id int)
RETURNS TABLE(account_no text, balance numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT a.account_no, a.balance
        FROM accounts a
        WHERE a.customer_id = p_customer_id
        ORDER BY a.balance DESC;
END;
$$;

Truy vấn động và cảnh báo SQL injection

Khi tên bảng/cột chỉ biết lúc chạy, bạn cần dựng câu SQL dưới dạng chuỗi và chạy bằng EXECUTE. Đây là chỗ cực kỳ dễ dính SQL injection nếu nối chuỗi bừa bãi.

Ba nguyên tắc:

1. Giá trị → truyền qua USING, đừng nối vào chuỗi. USING gửi tham số tách khỏi câu lệnh, an toàn tuyệt đối.
2. Định danh (tên bảng/cột) → format() với %I (identifier) — tự thêm dấu nháy kép và escape đúng.
3. Giá trị nhúng vào chuỗi (khi buộc phải) → %L (literal), hoặc quote_ident() / quote_literal().

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE FUNCTION dem_dong(p_bang text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    v_dem bigint;
BEGIN
    -- %I escape tên bảng an toàn; giá trị (nếu có) đi qua USING
    EXECUTE format('SELECT count(*) FROM %I', p_bang)
        INTO v_dem;
    RETURN v_dem;
END;
$$;

Ghi nhớ khẩu quyết: %I cho identifier, %L cho literal, USING cho tham số. Đừng bao giờ '... WHERE ten = ''' || p_ten || ''''.

Xử lý ngoại lệ

PL/pgSQL bắt lỗi bằng khối EXCEPTION gắn với một BEGIN ... END. Khi có lỗi trong khối, mọi thay đổi trong khối con đó được rollback (Postgres tạo một savepoint ngầm), rồi chuyển sang nhánh WHEN khớp.

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE FUNCTION chuyen_tien(p_from int, p_to int, p_amount numeric)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

    IF (SELECT balance FROM accounts WHERE id = p_from) < 0 THEN
        RAISE EXCEPTION 'So du khong du o tai khoan %', p_from
            USING ERRCODE = '23514';   -- check_violation
    END IF;

    RETURN 'OK';
EXCEPTION
    WHEN check_violation THEN
        RAISE NOTICE 'Bo qua chuyen tien: %', SQLERRM;
        RETURN 'FAILED: so du khong du';
    WHEN division_by_zero THEN
        RETURN 'FAILED: chia cho 0';
    WHEN OTHERS THEN
        -- SQLSTATE giu ma loi 5 ky tu; SQLERRM giu thong diep
        RAISE WARNING 'Loi khong luong truoc: % (%).', SQLERRM, SQLSTATE;
        RETURN 'FAILED';
END;
$$;

Vài điểm quan trọng:

• RAISE có nhiều mức: RAISE NOTICE (thông tin), RAISE WARNING, RAISE EXCEPTION (ném lỗi, dừng và rollback). % là chỗ chèn tham số.
• SQLSTATE là mã lỗi 5 ký tự chuẩn (ví dụ 23505 = unique_violation, 23514 = check_violation); SQLERRM là thông điệp. Bắt theo tên điều kiện (unique_violation) rõ ràng hơn bắt theo mã số.
• WHEN OTHERS bắt mọi thứ — dùng thận trọng, dễ nuốt mất lỗi thật. Nên log lại SQLSTATE trước khi xử lý.
• Khối EXCEPTION không miễn phí: mỗi lần vào là một savepoint. Trong vòng lặp nóng, tránh bọc EXCEPTION không cần thiết.

Function vs Procedure

Postgres phân biệt rõ hai thứ (procedure có từ PostgreSQL 11):

Điểm mấu chốt: procedure có thể chia công việc thành nhiều transaction. Đây là lý do procedure sinh ra — để viết job batch dài, commit theo lô, không giữ một transaction khổng lồ.

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE PROCEDURE xoa_log_cu(p_batch int)
LANGUAGE plpgsql
AS $$
DECLARE
    v_deleted int;
BEGIN
    LOOP
        DELETE FROM audit_log
        WHERE id IN (SELECT id FROM audit_log
                     WHERE created_at < now() - interval '1 year'
                     LIMIT p_batch);
        GET DIAGNOSTICS v_deleted = ROW_COUNT;
        COMMIT;                      -- commit sau mỗi lô, giai phong khoa
        EXIT WHEN v_deleted = 0;
    END LOOP;
END;
$$;
-- goi: CALL xoa_log_cu(10000);

Volatility — ảnh hưởng trực tiếp tới tối ưu. Function nên khai báo đúng một trong ba:

• IMMUTABLE — cùng input luôn cho cùng output, không đọc DB (vd: lower(text)). Planner được phép tính sẵn và cache; dùng được trong index biểu thức.
• STABLE — không đổi trong phạm vi một câu lệnh (vd: hàm đọc dữ liệu nhưng không sửa, phụ thuộc snapshot hiện tại).
• VOLATILE — mặc định; có thể trả kết quả khác mỗi lần (vd: random(), now() kiểu clock), hoặc có tác dụng phụ (sửa dữ liệu). Planner không được tối ưu bỏ bớt lời gọi.

Khai báo sai (ví dụ gắn IMMUTABLE cho hàm thực ra đọc bảng) sẽ cho kết quả sai vì planner cache nhầm. Khi nghi ngờ, để VOLATILE.

SECURITY DEFINER — cẩn thận. Mặc định function chạy với quyền của người gọi (SECURITY INVOKER). Đặt SECURITY DEFINER thì nó chạy với quyền của người tạo — hữu ích để cấp quyền hẹp (cho phép người dùng gọi một thao tác cụ thể mà không cần quyền trên bảng gốc), nhưng là bề mặt tấn công leo thang đặc quyền. Với SECURITY DEFINER, luôn cố định search_path (vd SET search_path = pg_catalog, public) trong định nghĩa function để tránh bị chiếm quyền qua việc dựng schema/hàm giả.

Trigger & trigger function

Trigger là function chạy tự động khi có INSERT/UPDATE/DELETE (hoặc TRUNCATE) trên một bảng. Trong Postgres, bạn viết một trigger function trả về kiểu đặc biệt TRIGGER, rồi gắn nó vào bảng bằng CREATE TRIGGER.

Các trục lựa chọn:

• Thời điểm: BEFORE (chạy trước, có thể sửa dòng hoặc huỷ thao tác), AFTER (chạy sau, thấy trạng thái cuối — hợp cho audit/lan truyền), INSTEAD OF (chỉ cho view, để làm view ghi được).
• Phạm vi: FOR EACH ROW (mỗi dòng bị ảnh hưởng chạy một lần) hay FOR EACH STATEMENT (một lần cho cả câu, bất kể số dòng).
• Biến ngầm trong trigger function: NEW (dòng mới, có ở INSERT/UPDATE), OLD (dòng cũ, có ở UPDATE/DELETE), cùng TG_OP (thao tác), TG_TABLE_NAME, v.v.

Quy tắc trả về của trigger BEFORE ... ROW: trả NEW để tiếp tục (có thể đã sửa NEW), trả NULL để bỏ qua thao tác trên dòng đó.

Sơ đồ tóm gọn: BEFORE đứng giữa câu lệnh và việc ghi thật — nơi lý tưởng để chuẩn hoá/xác thực/huỷ. AFTER chạy khi dòng đã yên vị và constraint đã qua — nơi lý tưởng để ghi audit hay lan truyền sang bảng khác.

Ví dụ đầy đủ: trigger audit ghi lịch sử thay đổi số dư

Đây là mẫu kinh điển: mỗi khi balance của một tài khoản đổi, ghi lại một dòng vào bảng audit_log.

-- (minh hoạ, không chạy trong sandbox)
-- Bảng audit (giả định đã tạo)
-- CREATE TABLE balance_audit(
--     id bigserial PRIMARY KEY,
--     account_id int, so_du_cu numeric, so_du_moi numeric,
--     doi_boi text, thay_doi_luc timestamptz DEFAULT now());

CREATE OR REPLACE FUNCTION ghi_audit_so_du()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- chi ghi khi so du thuc su doi
    IF NEW.balance IS DISTINCT FROM OLD.balance THEN
        INSERT INTO balance_audit(account_id, so_du_cu, so_du_moi, doi_boi)
        VALUES (OLD.id, OLD.balance, NEW.balance, current_user);
    END IF;
    RETURN NEW;         -- AFTER trigger: gia tri tra ve bi bo qua, nhung van tra NEW theo quy uoc
END;
$$;

CREATE TRIGGER trg_audit_so_du
    AFTER UPDATE OF balance ON accounts   -- chi kich hoat khi cot balance nam trong SET
    FOR EACH ROW
    EXECUTE FUNCTION ghi_audit_so_du();

Hai chi tiết đáng chú ý: IS DISTINCT FROM xử lý cả trường hợp NULL an toàn (không như <>); và AFTER UPDATE OF balance khiến trigger chỉ chạy khi câu UPDATE có đụng cột balance, tiết kiệm so với chạy mọi update.

Ứng dụng và cảnh báo về trigger

Trigger dùng tốt cho:

• Audit log — ghi ai đổi gì lúc nào, như ví dụ trên.
• Cột dẫn xuất — cập nhật updated_at, đồng bộ cột tổng hợp, dựng tsvector cho full-text search.
• Kiểm tra phức tạp — ràng buộc liên bảng mà CHECK không diễn đạt được.

Nhưng cẩn thận lạm dụng. Trigger chạy âm thầm: người đọc code app hoàn toàn không thấy chúng, nên khi dữ liệu "tự" biến đổi hay một UPDATE bỗng chậm/ném lỗi lạ, việc debug rất khổ. Trigger gọi trigger có thể tạo hiệu ứng dây chuyền khó lường, thậm chí vòng lặp. Quy tắc: dùng trigger cho những gì thật sự là bất biến dữ liệu (audit, toàn vẹn), đừng nhét quy trình nghiệp vụ vào đó. Và luôn ghi chú trong tài liệu bảng rằng có trigger nào đang canh giữ.

Use case thực tế

Audit số dư ngân hàng. Yêu cầu tuân thủ: mọi thay đổi số dư phải có vết, bất kể đến từ đâu. Nếu để tầng app tự ghi audit, thì một job batch hay một câu UPDATE sửa tay của DBA sẽ lọt lưới. Đặt trigger AFTER UPDATE OF balance như ví dụ trên là cách chắc chắn nhất: nó nằm ngay cạnh dữ liệu, không đường ghi nào tránh được. Đây đúng là loại toàn vẹn nên ở tầng DB.

Function tổng hợp cho báo cáo. Bộ phận vận hành cần "tổng số dư và số tài khoản của một khách hàng" ở nhiều nơi (dashboard, export, API). Thay vì mỗi chỗ tự viết lại một truy vấn (rồi lệch nhau), gói nó vào một function STABLE trả về composite:

-- (minh hoạ, không chạy trong sandbox)
CREATE OR REPLACE FUNCTION tong_hop_kh(p_customer_id int)
RETURNS TABLE(so_tk bigint, tong_so_du numeric)
LANGUAGE plpgsql
STABLE                                   -- chi doc, khong sua -> planner toi uu tot hon
AS $$
BEGIN
    RETURN QUERY
        SELECT count(*), coalesce(sum(balance), 0)
        FROM accounts
        WHERE customer_id = p_customer_id;
END;
$$;
-- dung nhu bang: SELECT * FROM tong_hop_kh(42);

Một định nghĩa, dùng khắp nơi, và khai báo STABLE cho planner biết nó chỉ đọc.

Các ngôn ngữ thủ tục khác

PL/pgSQL là mặc định, nhưng không phải lựa chọn duy nhất:

• SQL functions (LANGUAGE sql) — cho logic thuần SQL không cần biến/vòng lặp. Đơn giản, và từ Postgres 14 có cú pháp thân hàm chuẩn (BEGIN ATOMIC) giúp Postgres theo dõi phụ thuộc và tối ưu (inline) tốt hơn. Ưu tiên SQL function khi đủ dùng.
• PL/Python (plpython3u) — viết logic bằng Python, tiện cho tính toán/xử lý chuỗi phức tạp; là ngôn ngữ "untrusted" (hậu tố u), chỉ superuser cài được vì nó truy cập được hệ điều hành.
• PL/Perl (plperl — trusted, và plperlu — untrusted), cùng PL/Tcl, PL/v8 (JavaScript, qua extension)... mở rộng theo nhu cầu.

Các ngôn ngữ này được nạp như extension (CREATE EXTENSION plpython3u;) — chủ đề mà bài kế tiếp về Extension sẽ đào sâu.

Ghi nhớ

• Đặt vào DB thứ bản chất là toàn vẹn dữ liệu (audit, ràng buộc, cột dẫn xuất) và tác vụ set-based nặng nơi round-trip là nút cổ chai; giữ nghiệp vụ hay đổi ở tầng app để dễ test, version, đọc.
• PL/pgSQL = DECLARE/BEGIN/END, gán bằng :=, mượn kiểu bằng %TYPE/%ROWTYPE; trả về scalar, RECORD hay SETOF/TABLE.
• Trả nhiều dòng: RETURN QUERY (gọn, nhanh) hoặc RETURN NEXT (từng dòng). Lặp trên truy vấn bằng FOR r IN SELECT ... LOOP.
• SQL động: giá trị qua USING, tên bảng/cột qua format('%I', ...), literal qua %L. Đừng nối chuỗi thô — đó là cửa SQL injection.
• Ngoại lệ: BEGIN ... EXCEPTION WHEN ten_dieu_kien THEN ...; đọc SQLSTATE/SQLERRM; bắt WHEN OTHERS thì phải log. Khối EXCEPTION tốn savepoint — tránh trong vòng lặp nóng.
• Function vs Procedure: function gọi trong SQL, có trả về, không tự quản transaction; procedure gọi bằng CALL, COMMIT/ROLLBACK được bên trong — hợp cho job batch chia lô.
• Volatility: khai báo đúng IMMUTABLE/STABLE/VOLATILE — sai có thể cho kết quả sai do planner cache nhầm. Nghi ngờ thì để VOLATILE.
• SECURITY DEFINER cấp quyền hẹp nhưng là bề mặt leo thang đặc quyền — luôn cố định search_path.
• Trigger: BEFORE để chuẩn hoá/xác thực/huỷ (trả NULL để bỏ qua), AFTER để audit/lan truyền, INSTEAD OF cho view. Biến NEW/OLD, hàm trả TRIGGER. Dùng AFTER UPDATE OF cot để chỉ chạy khi cần.
• Trigger mạnh nhưng âm thầm và khó debug — dùng cho toàn vẹn dữ liệu, đừng nhét quy trình nghiệp vụ, và luôn ghi lại là bảng có trigger.
• Ưu tiên SQL function khi đủ; PL/Python/PL/Perl khi cần sức mạnh ngôn ngữ đa dụng (nhớ trusted vs untrusted).

Đọc tiếp

• PostgreSQL 4 — SQL nâng cao — nền tảng truy vấn mà function/trigger của bạn dựa lên.
• PostgreSQL 6 — Extension — cài PL/Python và mở rộng khả năng của server.
• PostgreSQL 8 — MVCC & concurrency — hiểu vì sao trigger AFTER, savepoint và COMMIT trong procedure hành xử như vậy.