Procedure, Function & TriggerProcedures, Functions & Triggers

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

PostgreSQL cho phép đưa logic xử lý vào ngay trong database, thay vì (hoặc bên cạnh) tầng application. Có ba khối xây dựng chính giúp làm điều này: function, trả về một giá trị và có thể nhúng thẳng vào SELECT, WHERE, hay JOIN; procedure (thêm từ PostgreSQL 11), chạy một chuỗi bước và — khác với function — có thể tự quản lý transaction của chính nó bằng COMMIT/ROLLBACK; và trigger, gắn một function vào một table để tự động kích hoạt khi có INSERT/UPDATE/DELETE/TRUNCATE.

Cả ba đều thường được viết bằng PL/pgSQL, ngôn ngữ thủ tục (procedural language) built-in của PostgreSQL — về bản chất là SQL được bọc thêm biến, câu lệnh điều kiện, vòng lặp, và xử lý ngoại lệ. Hiểu PL/pgSQL là điều kiện tiên quyết cho mọi thứ còn lại trong bài này, nên chúng ta sẽ bắt đầu từ đó trước khi đi vào function, procedure, volatility và trigger.

Bài viết này giả định bạn đã quen với các data type và schema object cơ bản (xem ./03-data-types-and-schema-objects.md) cũng như các kiến thức nền tảng về truy vấn SQL (xem ./04-querying-data-sql-fundamentals.md). Một số đánh đổi về schema design liên quan đến trigger (denormalization, computed column) được bàn sâu hơn ở ./17-schema-design-patterns-and-antipatterns.md.

Kiến thức nền tảng

PL/pgSQL cơ bản

Phần thân (body) của một function hoặc procedure viết bằng PL/pgSQL là một block được quote bằng $$ (hoặc bất kỳ dollar-quote tag tùy chỉnh nào, ví dụ $body$) với cấu trúc:

[ <<label>> ]
[ DECLARE
    -- khai báo biến
]
BEGIN
    -- các câu lệnh
[ EXCEPTION
    WHEN condition THEN
        -- xử lý ngoại lệ
]
END [ label ];

Biến (variable) được khai báo trong phần DECLARE với một type, và tùy chọn giá trị mặc định:

DECLARE
    v_customer_id  integer;
    v_full_name    text := 'unknown';
    v_price        numeric(10,2) := 0.00;
    v_row          orders%ROWTYPE;   -- kế thừa cấu trúc row của table "orders"
    v_count        int;

%TYPE và %ROWTYPE là hai idiom quan trọng: v_customer_id customers.id%TYPE gắn type của biến với một column, nhờ đó nếu type của column đó thay đổi thì biến vẫn tự động đồng bộ theo.

Câu lệnh điều kiện dùng IF ... THEN ... ELSIF ... ELSE ... END IF, hoặc CASE:

IF v_price < 0 THEN
    RAISE EXCEPTION 'price cannot be negative: %', v_price;
ELSIF v_price = 0 THEN
    v_full_name := v_full_name || ' (free)';
ELSE
    v_full_name := v_full_name || ' (paid)';
END IF;

Vòng lặp có ba dạng phổ biến:

-- FOR trên một khoảng
FOR i IN 1..5 LOOP
    RAISE NOTICE 'iteration %', i;
END LOOP;

-- FOR trên kết quả một query (phổ biến nhất trong thực tế)
FOR v_row IN SELECT * FROM orders WHERE status = 'pending' LOOP
    RAISE NOTICE 'order % is pending', v_row.id;
END LOOP;

-- WHILE
WHILE v_count > 0 LOOP
    v_count := v_count - 1;
END LOOP;

-- LOOP trần với EXIT tường minh
LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count >= 10;
END LOOP;

RAISE được dùng cả để in thông tin chẩn đoán lẫn để raise lỗi:

RAISE NOTICE 'processing customer %', v_customer_id;   -- thông tin, hiển thị cho client nếu log level cho phép
RAISE WARNING 'unexpected state for order %', v_row.id;
RAISE EXCEPTION 'invalid status: %', v_row.status
    USING ERRCODE = '22023', HINT = 'status must be one of pending/shipped/cancelled';

RAISE EXCEPTION sẽ hủy transaction block hiện tại (hoặc bị bắt bởi một EXCEPTION handler bao bọc bên ngoài); NOTICE và WARNING không gây fatal.

Ví dụ function hoàn chỉnh: loop + aggregation + RETURN

CREATE OR REPLACE FUNCTION total_revenue_for_customer(p_customer_id integer)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    v_order      record;
    v_total      numeric(12,2) := 0;
BEGIN
    FOR v_order IN
        SELECT o.id, o.total_amount, o.status
        FROM orders o
        WHERE o.customer_id = p_customer_id
    LOOP
        IF v_order.status = 'cancelled' THEN
            CONTINUE;               -- bỏ qua order đã cancelled
        END IF;

        v_total := v_total + v_order.total_amount;
    END LOOP;

    RETURN v_total;
END;
$$;

Cách dùng — vì đây là function nên có thể gọi ở bất cứ đâu một scalar expression được chấp nhận:

SELECT c.name, total_revenue_for_customer(c.id) AS revenue
FROM customers c
ORDER BY revenue DESC;

SELECT * FROM customers WHERE total_revenue_for_customer(id) > 1000;

Function

CREATE FUNCTION khai báo tên, danh sách argument, return type, ngôn ngữ, và body:

CREATE OR REPLACE FUNCTION full_name(p_first text, p_last text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN trim(p_first) || ' ' || trim(p_last);
END;
$$;

Function cũng có thể trả về một tập hợp row (table-valued function), rất phổ biến khi cần một “view có tham số” tái sử dụng được:

CREATE OR REPLACE FUNCTION orders_above(p_min_amount numeric)
RETURNS TABLE(id integer, customer_id integer, total_amount numeric)
LANGUAGE sql
STABLE
AS $$
    SELECT o.id, o.customer_id, o.total_amount
    FROM orders o
    WHERE o.total_amount >= p_min_amount;
$$;

SELECT * FROM orders_above(500);

Chú ý biến thể LANGUAGE sql ở trên — với logic đơn giản, chỉ một câu query, một SQL function thường rõ ràng hơn và cho phép planner inline nó mạnh mẽ hơn so với một PL/pgSQL function.

Function có thể được gọi từ SELECT, WHERE, JOIN ... ON, generated column, CHECK constraint, index expression, và nhiều nơi khác — bất cứ đâu một value expression được phép — nhưng một lệnh gọi function bình thường không thể issue COMMIT hoặc ROLLBACK. Function luôn chạy bên trong transaction của caller.

Procedure

Procedure (PostgreSQL 11+) được khai báo bằng CREATE PROCEDURE và gọi bằng CALL, không phải SELECT. Đặc điểm định nghĩa của nó là có thể tự quản lý transaction bằng COMMIT và ROLLBACK — điều mà function về bản chất không được phép làm, vì bộ thực thi query đang chạy lệnh gọi function đã nằm bên trong một transaction/snapshot mà nó không thể phá vỡ giữa chừng một expression.

CREATE OR REPLACE PROCEDURE archive_old_orders(p_batch_size integer DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rows_moved integer;
BEGIN
    LOOP
        WITH moved AS (
            DELETE FROM orders
            WHERE id IN (
                SELECT id FROM orders
                WHERE status = 'completed'
                  AND completed_at < now() - interval '1 year'
                LIMIT p_batch_size
            )
            RETURNING *
        )
        INSERT INTO orders_archive
        SELECT * FROM moved;

        GET DIAGNOSTICS v_rows_moved = ROW_COUNT;

        RAISE NOTICE 'archived % rows', v_rows_moved;

        COMMIT;    -- chỉ hợp lệ vì đây là PROCEDURE, và chỉ khi được gọi
                   -- ở top level (không lồng bên trong một transaction block khác)

        EXIT WHEN v_rows_moved < p_batch_size;
    END LOOP;
END;
$$;

CALL archive_old_orders(5000);

Đây chính xác là tình huống roadmap nhắc tới: một batch job chạy dài cần di chuyển hàng triệu row mà không giữ một transaction khổng lồ mở suốt (điều này sẽ làm WAL phình to, giữ lock, và cản trở vacuum thu hồi dead tuple). Commit định kỳ bên trong loop cho phép từng batch trở nên visible và durable một cách độc lập. Một function không thể làm được điều này — toàn bộ vòng FOR trong total_revenue_for_customer ở trên chạy dưới một snapshot duy nhất, không có khả năng commit giữa chừng.

COMMIT/ROLLBACK bên trong một procedure chỉ được phép khi procedure đó được gọi trực tiếp bằng CALL ở top level (không phải từ bên trong một function, không phải từ bên trong một transaction-controlled block khác, và không phải từ một trigger). Sau khi COMMIT bên trong một procedure, một transaction mới tự động bắt đầu cho phần còn lại của procedure body.

Bảng so sánh Function vs. Procedure

Khía cạnh	Function	Procedure
Khai báo bằng	CREATE FUNCTION	CREATE PROCEDURE
Gọi bằng	SELECT fn(...), hoặc nhúng trong expression	CALL proc(...)
Trả về giá trị	Có (RETURNS type, scalar/set/table/void)	Không (không có mệnh đề RETURNS; dùng tham số OUT/INOUT thay thế)
Dùng được trong SELECT/WHERE/JOIN	Có	Không
Tự COMMIT/ROLLBACK được	Không — chạy hoàn toàn bên trong transaction của caller	Có, khi được gọi ở top level qua CALL
Trường hợp dùng điển hình	Tính toán, validation, table-valued helper, index expression	Batch/ETL job, tác vụ bảo trì, workflow nhiều bước cần checkpoint
Dùng làm trigger function được không	Có (trigger function chính là một function trả về trigger, xem phần dưới)	Không — trigger function bắt buộc phải là FUNCTION trả về trigger, không phải procedure
Xuất hiện từ	Mọi phiên bản	PostgreSQL 11

Dòng cuối cùng đáng chú thích thêm: dù procedure đã có thêm khả năng điều khiển transaction, trình xử lý trigger vẫn bắt buộc phải là function (trả về pseudo-type đặc biệt trigger), vì nó thực thi như một phần của transaction thuộc câu lệnh kích hoạt và không được phép cố commit hay rollback transaction đó.

Khái niệm chính

Volatility của function: IMMUTABLE, STABLE, VOLATILE

Mỗi function đều có một hạng mục volatility cho query planner biết nó được phép tối ưu hóa lệnh gọi tới function đó mạnh mẽ đến mức nào. Điều này được khai báo bằng một keyword trong CREATE FUNCTION:

• IMMUTABLE — với cùng argument, function luôn trả về cùng kết quả, mãi mãi, không phụ thuộc vào trạng thái database. Ví dụ: upper(text), một function tính toán số học thuần túy, một function chỉ nhìn vào argument của chính nó và các hằng số.
• STABLE — function có thể đọc từ database (table, setting hiện tại) nhưng được đảm bảo không thay đổi database, và trả về cùng kết quả với cùng argument trong phạm vi một câu lệnh/scan. Ví dụ: một function tra tỷ giá từ table rates, hoặc gọi current_date/current_setting.
• VOLATILE (mặc định nếu không khai báo) — kết quả của function có thể thay đổi giữa các lần gọi dù cùng argument, và/hoặc có side effect (ghi dữ liệu, tăng sequence, RAISE, gọi random(), nextval(), sửa table). PostgreSQL phải gọi nó một lần cho mỗi row/lệnh gọi và không được cache hay đổi thứ tự các lệnh gọi này.

CREATE OR REPLACE FUNCTION age_in_years(p_birthdate date)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT EXTRACT(YEAR FROM age(current_date, p_birthdate))::integer;
$$;

Khoan — thoạt nhìn function này có vẻ immutable (chỉ là phép tính toán học thuần túy trên input), nhưng nó âm thầm phụ thuộc vào current_date, thứ thay đổi mỗi ngày. Đây chính xác là kiểu bug tinh vi mà roadmap cảnh báo: khai báo một function là IMMUTABLE trong khi thực ra nó phụ thuộc vào thứ gì đó nằm ngoài argument của nó. Khai báo đúng ở đây phải là STABLE (nó không ghi gì cả, nhưng kết quả của nó phụ thuộc vào current_date, thứ thay đổi giữa các lần gọi ở thời điểm khác nhau):

CREATE OR REPLACE FUNCTION age_in_years(p_birthdate date)
RETURNS integer
LANGUAGE sql
STABLE
AS $$
    SELECT EXTRACT(YEAR FROM age(current_date, p_birthdate))::integer;
$$;

Tại sao điều này quan trọng trong thực tế:

• Nếu bạn đánh dấu một function là IMMUTABLE trong khi nó không phải vậy, PostgreSQL có thể xây dựng một index trên expression dùng function đó, hoặc gộp các lệnh gọi lặp lại thành một hằng số trong quá trình planning, và giá trị được cache/index đó âm thầm trở nên lỗi thời (stale). Một sự cố thực tế kinh điển: một function IMMUTABLE format một timestamp dùng setting timezone của session — đổi timezone, và một functional index xây dựng dựa trên nó trở nên sai mà không hề có lỗi nào được báo.
• Ngược lại, đánh dấu một thứ là VOLATILE trong khi nó thực ra STABLE hoặc IMMUTABLE không gây bug về tính đúng đắn, chỉ là bỏ lỡ cơ hội tối ưu — planner sẽ đánh giá lại function thường xuyên hơn nhiều so với cần thiết (ví dụ, một lần cho mỗi row được scan thay vì một lần cho mỗi query), điều này có thể là chi phí hiệu năng thực sự trong một mệnh đề WHERE hoặc một SELECT lớn.
• Function STABLE/IMMUTABLE là ứng viên để dùng trong một functional index và có thể được inline/constant-fold bởi planner; function VOLATILE thì không bao giờ, vì làm vậy có thể thay đổi hành vi quan sát được (ví dụ, một function VOLATILE gọi nextval() phải chạy đúng một lần cho mỗi row).

-- Sử dụng an toàn một functional index — function phải ít nhất là STABLE
CREATE OR REPLACE FUNCTION normalize_email(p_email text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT lower(trim(p_email));
$$;

CREATE INDEX idx_users_email_normalized ON users (normalize_email(email));

normalize_email thực sự là immutable: lower/trim trên một argument kiểu text không phụ thuộc vào gì ngoài chính argument đó (bỏ qua các trường hợp ngoại lệ về collation), nên index này an toàn và planner có thể dùng nó cho WHERE normalize_email(email) = 'foo@bar.com'.

Trigger

Một trigger gắn một trigger function vào một table (hoặc view, với INSTEAD OF) để nó tự động chạy quanh một sự kiện thay đổi dữ liệu. Ba chiều xác định hành vi của trigger:

Thời điểm (timing):

• BEFORE — kích hoạt trước khi thao tác được áp dụng; trigger function có thể kiểm tra và sửa row sắp được ghi (bằng cách thay đổi và return NEW), hoặc hủy hoàn toàn thao tác bằng cách return NULL.
• AFTER — kích hoạt sau khi thao tác đã được áp dụng; thường dùng để logging/auditing/gây side effect kế tiếp, vì row đã được commit vào table (trong phạm vi transaction) tại thời điểm trigger chạy. Trigger AFTER không thể thay đổi row đang được ghi (return một giá trị NEW khác không có tác dụng gì, vì việc ghi đã xảy ra rồi).
• INSTEAD OF — chỉ hợp lệ trên view; thay thế hoàn toàn thao tác, cho phép một view vốn không update được có thể hỗ trợ INSERT/UPDATE/DELETE bằng cách chuyển thao tác đó thành logic tác động lên base table.

Độ chi tiết (granularity):

• FOR EACH ROW — trigger function chạy một lần cho mỗi row bị ảnh hưởng, với NEW/OLD gắn với row đó.
• FOR EACH STATEMENT — trigger function chỉ chạy một lần cho toàn bộ câu lệnh, bất kể có bao nhiêu row bị ảnh hưởng; NEW/OLD không có sẵn dưới dạng biến single-row (mặc dù từ PostgreSQL 10+ có thể truy cập toàn bộ tập row bị ảnh hưởng qua transition table, REFERENCING NEW TABLE AS ... OLD TABLE AS ...).

Các biến đặc biệt NEW và OLD chỉ có sẵn bên trong một trigger function ở mức row:

Sự kiện trigger	OLD có sẵn	NEW có sẵn
INSERT	không	có
UPDATE	có	có
DELETE	có	không

Ví dụ thực tế: trigger tự động cập nhật updated_at

Một pattern rất phổ biến — giữ column updated_at luôn cập nhật mỗi khi có UPDATE, mà không phụ thuộc vào application code phải nhớ set nó:

CREATE TABLE products (
    id           serial PRIMARY KEY,
    name         text NOT NULL,
    price        numeric(10,2) NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_set_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

Chú ý hình dạng của trigger function: nó không nhận argument tường minh theo nghĩa SQL, bắt buộc phải khai báo RETURNS trigger, và đọc/ghi row thông qua NEW/OLD (cả hai đều tự động có sẵn bên trong trigger function body, không được truyền như tham số). Vì đây là BEFORE, việc thay đổi NEW.updated_at và return NEW thực sự làm thay đổi những gì được ghi xuống đĩa.

UPDATE products SET price = 19.99 WHERE id = 1;
-- updated_at giờ đã cập nhật, dù câu lệnh UPDATE không hề nhắc tới nó

Ví dụ thực tế: trigger audit-log

Trường hợp dùng kinh điển thứ hai — ghi lại mọi thay đổi trên một table vào một history table dạng append-only, dùng AFTER (vì ta chỉ quan sát, không thay đổi việc ghi) và một trigger function tổng quát, dùng chung cho nhiều table nhờ TG_TABLE_NAME, TG_OP, và row_to_json:

CREATE TABLE audit_log (
    id            bigserial PRIMARY KEY,
    table_name    text NOT NULL,
    operation     text NOT NULL,
    old_data      jsonb,
    new_data      jsonb,
    changed_by    text NOT NULL DEFAULT current_user,
    changed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_row_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_row_change();

TG_OP, TG_TABLE_NAME, TG_WHEN, TG_LEVEL, và TG_ARGV[] là các biến ngầm định mà PostgreSQL tự điền vào bên trong mọi trigger function, cho phép một function tổng quát phục vụ nhiều table và nhiều thao tác. Function này có thể tái sử dụng đơn giản bằng cách tạo cùng câu lệnh CREATE TRIGGER trên bất kỳ table nào khác bạn muốn audit.

Các trường hợp dùng trigger phổ biến

• Duy trì các column denormalized hoặc computed — ví dụ giữ column order_total trên row cha orders đồng bộ với tổng của các order_items, hoặc duy trì column tsvector cho full-text search qua tsvector_update_trigger. (Xem ./17-schema-design-patterns-and-antipatterns.md để biết khi nào denormalization là ý hay và khi nào không.)
• Audit các thay đổi — pattern audit_log ở trên, hoặc các extension/pattern chuyên biệt hơn của PostgreSQL cho temporal table.
• Thực thi các business rule phức tạp mà một CHECK constraint không thể diễn đạt được vì chúng cần tra cứu xuyên row hoặc xuyên table (CHECK constraint chỉ nhìn thấy row đang được validate, không bao giờ thấy các row khác) — ví dụ “manager của một employee phải cùng phòng ban,” được kiểm tra qua một trigger BEFORE INSERT OR UPDATE chạy một SELECT trên table khác và RAISE EXCEPTION nếu vi phạm rule.
• Kích hoạt logic tùy chỉnh theo dây chuyền (cascading) — ví dụ tự động tạo một row wallets mỗi khi có một row users mới được insert, hoặc archive các row con liên quan trước khi xóa row cha khi chỉ riêng ON DELETE CASCADE không đủ diễn đạt.

Lưu ý: trigger là side effect vô hình

Mặt trái của việc “trigger tự động chạy” là chúng chạy vô hình từ góc nhìn của người viết câu lệnh gây kích hoạt. Một câu UPDATE products SET price = 19.99 WHERE id = 1; trông có vẻ đơn giản có thể âm thầm: cập nhật updated_at, ghi một row vào audit_log, tính lại một tổng vật lý hóa trên table cha, gửi một NOTIFY, và fail với một exception từ một rule hoàn toàn khác ở table cách đó ba bước. Ai đó chỉ đọc application code phát ra câu UPDATE sẽ không có cách nào biết bất kỳ điều gì trong số này đã xảy ra nếu không biết phải nhìn vào schema.

Điều này khiến trigger trở thành một công cụ hai lưỡi mà DBA phải cân nhắc:

• Debug trở nên khó hơn. Một câu UPDATE chậm hoặc fail có thể thực ra chậm hoặc fail vì một trigger đang làm công việc không liên quan, chứ không phải vì bản thân câu lệnh. EXPLAIN ANALYZE trên một câu lệnh có trigger sẽ hiển thị thời gian thực thi trigger như một dòng riêng, thường là manh mối đầu tiên.
• Thứ tự thực thi quan trọng và dễ sai khi có nhiều trigger trên cùng table/event — PostgreSQL kích hoạt chúng theo thứ tự tên (alphabet) cho cùng timing/event, đây là nguồn gốc phổ biến của sự bối rối “tại sao trigger B chạy trước trigger A”. Đặt prefix cho tên trigger để chủ động kiểm soát thứ tự (ví dụ trg_01_..., trg_02_...) nếu thứ tự có ý nghĩa.
• Trigger đệ quy/dây chuyền qua nhiều table có thể tạo ra các chuỗi side effect khó lần theo, và trong trường hợp xấu nhất, tạo vòng lặp vô hạn chỉ được chặn bởi kiểm tra pg_trigger_depth().

Khuyến nghị best-practice: ghi tài liệu cho mục đích của mỗi trigger trực tiếp bằng comment (COMMENT ON TRIGGER ... IS '...') và giữ một quy ước đặt tên thể hiện rõ ý định (ví dụ trg_<table>_<purpose>). Tránh dùng trigger để thay thế cho business logic ở tầng application vốn không liên quan gì đến tính toàn vẹn dữ liệu — logic thực chất thuộc về workflow, thông báo tới hệ thống bên ngoài, hay validation hướng tới UI thường nên nằm ở application, nơi nó hiển thị được trong code review, test được, và trace được trong stack trace. Chỉ dành trigger cho logic phải luôn đúng bất kể client nào chạm vào dữ liệu — tính toàn vẹn dữ liệu, audit, và các invariant mà bản thân database phải chịu trách nhiệm đảm bảo.

System catalog: pg_catalog vs. information_schema

PostgreSQL lưu trữ metadata về mọi object nó quản lý — table, column, function, type, trigger, index, constraint, role, và nhiều hơn nữa — trong một tập hợp các system table nằm trong schema pg_catalog. Đây là cách database tự mô tả chính nó: \d trong psql, pg_dump, và query planner đều cuối cùng đọc từ pg_catalog.

Có hai cách để introspect metadata này:

• information_schema — một tập hợp view được định nghĩa bởi chuẩn SQL, có mặt (với một số khác biệt) trên nhiều hệ quản trị database. Query trên nó portable hơn qua các phiên bản PostgreSQL và thậm chí qua các sản phẩm database khác nhau, nhưng tất yếu không thể phơi bày bất kỳ thứ gì đặc thù của PostgreSQL (như tablespace, hay thứ tự kích hoạt trigger chính xác của PostgreSQL) vì chuẩn không định nghĩa các khái niệm đó.
• pg_catalog — các catalog table gốc của PostgreSQL (pg_class, pg_attribute, pg_proc, pg_trigger, pg_constraint, v.v.), luôn đầy đủ, luôn chính xác, và phơi bày mọi chi tiết đặc thù của PostgreSQL, đổi lại là gắn chặt với PostgreSQL và kém ổn định hơn qua các major version so với các view theo chuẩn SQL.

Quy tắc thực dụng: ưu tiên information_schema trước cho việc introspect đơn giản, portable; chuyển sang pg_catalog khi cần thứ mà information_schema không phơi bày (thứ tự kích hoạt trigger, trigger có đang enable hay không, OID nội bộ, chủ sở hữu extension, v.v.).

Liệt kê trigger trên một table qua information_schema.triggers:

SELECT trigger_name,
       event_manipulation,   -- INSERT / UPDATE / DELETE
       action_timing,        -- BEFORE / AFTER
       action_orientation,   -- ROW / STATEMENT
       action_statement      -- mệnh đề EXECUTE FUNCTION
FROM information_schema.triggers
WHERE event_object_table = 'orders'
ORDER BY trigger_name;

Chú ý một điểm oái oăm: information_schema.triggers báo cáo một row cho mỗi event với trigger nhiều event (AFTER INSERT OR UPDATE OR DELETE), nên một CREATE TRIGGER duy nhất có thể xuất hiện thành ba row ở đây.

Cùng query đó nhưng qua pg_catalog trực tiếp, không bị điểm oái oăm nói trên và phơi bày nhiều chi tiết hơn (trạng thái enable/disable, OID function chính xác, có phải constraint trigger hay không):

SELECT t.tgname                                   AS trigger_name,
       c.relname                                  AS table_name,
       p.proname                                  AS function_name,
       t.tgenabled                                AS enabled_state,   -- 'O' = enabled, 'D' = disabled
       CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END     AS granularity,
       CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE'
            WHEN t.tgtype & 64 = 64 THEN 'INSTEAD OF'
            ELSE 'AFTER' END                       AS timing
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_proc p  ON p.oid = t.tgfoid
WHERE c.relname = 'orders'
  AND NOT t.tgisinternal    -- loại bỏ các trigger nội bộ đứng sau FK constraint, v.v.
ORDER BY t.tgname;

Bộ lọc NOT t.tgisinternal rất quan trọng: PostgreSQL cài đặt các hành động ON DELETE/ON UPDATE của foreign key và một số kiểm tra constraint dưới dạng system trigger nội bộ trong pg_trigger, và nếu không có bộ lọc này, chúng sẽ làm nhiễu kết quả của bất kỳ query nào trên pg_trigger.

Bạn cũng có thể kiểm tra volatility đã khai báo của một function theo cách tương tự:

SELECT proname,
       CASE provolatile
           WHEN 'i' THEN 'IMMUTABLE'
           WHEN 's' THEN 'STABLE'
           WHEN 'v' THEN 'VOLATILE'
       END AS volatility
FROM pg_proc
WHERE proname = 'age_in_years';

Best Practices

• Ưu tiên function hơn procedure trừ khi thực sự cần điều khiển transaction. Chỉ dùng procedure khi workload thực sự cần COMMIT giữa chừng — archival theo batch, backfill hàng loạt, ETL job chạy dài — không phải như một thói quen mặc định.
• Mặc định dùng VOLATILE khi còn phân vân. Nó luôn đúng, chỉ có thể chậm hơn. Chỉ nâng một function lên STABLE hay IMMUTABLE sau khi đã xác minh nó thực sự không phụ thuộc vào bất cứ gì ngoài argument của nó (với IMMUTABLE) hoặc không bao giờ thay đổi database (với STABLE); một khai báo IMMUTABLE sai là một bug về tính đúng đắn, không chỉ là bỏ lỡ tối ưu.
• Dùng function SECURITY DEFINER một cách tiết kiệm và thận trọng. Một function đánh dấu SECURITY DEFINER chạy với quyền của owner, không phải của caller — hữu ích cho việc nâng quyền có kiểm soát (ví dụ cho một role ít quyền update một row qua một function hẹp), nhưng luôn ghim search_path tường minh bên trong các function như vậy để tránh tấn công search-path-hijacking.
• Đặt tên và comment rõ ràng cho mọi trigger, và áp dụng một quy ước đặt tên (trg_<table>_<action>) cũng thể hiện thứ tự kích hoạt khi có nhiều trigger trên cùng một event.
• Giữ trigger function nhỏ gọn và nhanh, đặc biệt là trigger BEFORE ROW trên đường ghi dữ liệu nóng — chúng chạy đồng bộ bên trong mọi INSERT/UPDATE/DELETE bị ảnh hưởng, và logic trigger chậm sẽ trở thành độ trễ vô hình trên câu lệnh gọi.
• Tránh các chuỗi trigger đệ quy/dây chuyền qua nhiều table; khi có thể, ưu tiên trigger AFTER STATEMENT với transition table thay vì logic theo từng row khi xử lý các thao tác hàng loạt, vì một trigger FOR EACH ROW kích hoạt trên một UPDATE hàng loạt 500.000 row sẽ chạy 500.000 lần.
• Dùng information_schema cho tooling portable, hướng ra bên ngoài (ORM, migration tool, sinh tài liệu) và pg_catalog khi cần chi tiết đặc thù PostgreSQL hoặc hiệu năng tốt hơn cho tooling nặng về introspection.
• Test trigger một cách tường minh, không chỉ test các table mà chúng gắn vào — một bộ test pass cho “insert một row” có thể che giấu một audit trigger bị hỏng đã âm thầm nuốt mất một exception.

Tài liệu tham khảo

• PostgreSQL Docs — PL/pgSQL
• PostgreSQL Docs — CREATE FUNCTION
• PostgreSQL Docs — CREATE PROCEDURE
• PostgreSQL Docs — Trigger Behavior
• PostgreSQL Docs — CREATE TRIGGER
• PostgreSQL Docs — PL/pgSQL Trigger Procedures
• PostgreSQL Docs — information_schema
• PostgreSQL Docs — System Catalogs (pg_trigger, pg_proc)

[ <<label>> ]
[ DECLARE
    -- variable declarations
]
BEGIN
    -- statements
[ EXCEPTION
    WHEN condition THEN
        -- handler
]
END [ label ];

DECLARE
    v_customer_id  integer;
    v_full_name    text := 'unknown';
    v_price        numeric(10,2) := 0.00;
    v_row          orders%ROWTYPE;   -- inherits the row structure of table "orders"
    v_count        int;

IF v_price < 0 THEN
    RAISE EXCEPTION 'price cannot be negative: %', v_price;
ELSIF v_price = 0 THEN
    v_full_name := v_full_name || ' (free)';
ELSE
    v_full_name := v_full_name || ' (paid)';
END IF;

-- FOR over a range
FOR i IN 1..5 LOOP
    RAISE NOTICE 'iteration %', i;
END LOOP;

-- FOR over a query result (most common in practice)
FOR v_row IN SELECT * FROM orders WHERE status = 'pending' LOOP
    RAISE NOTICE 'order % is pending', v_row.id;
END LOOP;

-- WHILE
WHILE v_count > 0 LOOP
    v_count := v_count - 1;
END LOOP;

-- Bare LOOP with an explicit EXIT
LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count >= 10;
END LOOP;

RAISE NOTICE 'processing customer %', v_customer_id;   -- informational, visible to client if log level allows
RAISE WARNING 'unexpected state for order %', v_row.id;
RAISE EXCEPTION 'invalid status: %', v_row.status
    USING ERRCODE = '22023', HINT = 'status must be one of pending/shipped/cancelled';

CREATE OR REPLACE FUNCTION total_revenue_for_customer(p_customer_id integer)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    v_order      record;
    v_total      numeric(12,2) := 0;
BEGIN
    FOR v_order IN
        SELECT o.id, o.total_amount, o.status
        FROM orders o
        WHERE o.customer_id = p_customer_id
    LOOP
        IF v_order.status = 'cancelled' THEN
            CONTINUE;               -- skip cancelled orders
        END IF;

        v_total := v_total + v_order.total_amount;
    END LOOP;

    RETURN v_total;
END;
$$;

SELECT c.name, total_revenue_for_customer(c.id) AS revenue
FROM customers c
ORDER BY revenue DESC;

SELECT * FROM customers WHERE total_revenue_for_customer(id) > 1000;

CREATE OR REPLACE FUNCTION full_name(p_first text, p_last text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN trim(p_first) || ' ' || trim(p_last);
END;
$$;

CREATE OR REPLACE FUNCTION orders_above(p_min_amount numeric)
RETURNS TABLE(id integer, customer_id integer, total_amount numeric)
LANGUAGE sql
STABLE
AS $$
    SELECT o.id, o.customer_id, o.total_amount
    FROM orders o
    WHERE o.total_amount >= p_min_amount;
$$;

SELECT * FROM orders_above(500);

CREATE OR REPLACE PROCEDURE archive_old_orders(p_batch_size integer DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rows_moved integer;
BEGIN
    LOOP
        WITH moved AS (
            DELETE FROM orders
            WHERE id IN (
                SELECT id FROM orders
                WHERE status = 'completed'
                  AND completed_at < now() - interval '1 year'
                LIMIT p_batch_size
            )
            RETURNING *
        )
        INSERT INTO orders_archive
        SELECT * FROM moved;

        GET DIAGNOSTICS v_rows_moved = ROW_COUNT;

        RAISE NOTICE 'archived % rows', v_rows_moved;

        COMMIT;    -- only legal because this is a PROCEDURE, and only when called
                   -- at the top level (not nested inside another transaction block)

        EXIT WHEN v_rows_moved < p_batch_size;
    END LOOP;
END;
$$;

CALL archive_old_orders(5000);

CREATE OR REPLACE FUNCTION age_in_years(p_birthdate date)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT EXTRACT(YEAR FROM age(current_date, p_birthdate))::integer;
$$;

CREATE OR REPLACE FUNCTION age_in_years(p_birthdate date)
RETURNS integer
LANGUAGE sql
STABLE
AS $$
    SELECT EXTRACT(YEAR FROM age(current_date, p_birthdate))::integer;
$$;

-- Safe use of a functional index — the function must be at least STABLE
CREATE OR REPLACE FUNCTION normalize_email(p_email text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT lower(trim(p_email));
$$;

CREATE INDEX idx_users_email_normalized ON users (normalize_email(email));

CREATE TABLE products (
    id           serial PRIMARY KEY,
    name         text NOT NULL,
    price        numeric(10,2) NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_set_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

UPDATE products SET price = 19.99 WHERE id = 1;
-- updated_at is now current, even though the UPDATE statement never mentioned it

CREATE TABLE audit_log (
    id            bigserial PRIMARY KEY,
    table_name    text NOT NULL,
    operation     text NOT NULL,
    old_data      jsonb,
    new_data      jsonb,
    changed_by    text NOT NULL DEFAULT current_user,
    changed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_row_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_row_change();

SELECT trigger_name,
       event_manipulation,   -- INSERT / UPDATE / DELETE
       action_timing,        -- BEFORE / AFTER
       action_orientation,   -- ROW / STATEMENT
       action_statement      -- the EXECUTE FUNCTION clause
FROM information_schema.triggers
WHERE event_object_table = 'orders'
ORDER BY trigger_name;

SELECT t.tgname                                   AS trigger_name,
       c.relname                                  AS table_name,
       p.proname                                  AS function_name,
       t.tgenabled                                AS enabled_state,   -- 'O' = enabled, 'D' = disabled
       CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END     AS granularity,
       CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE'
            WHEN t.tgtype & 64 = 64 THEN 'INSTEAD OF'
            ELSE 'AFTER' END                       AS timing
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_proc p  ON p.oid = t.tgfoid
WHERE c.relname = 'orders'
  AND NOT t.tgisinternal    -- exclude internal triggers backing FK constraints, etc.
ORDER BY t.tgname;

SELECT proname,
       CASE provolatile
           WHEN 'i' THEN 'IMMUTABLE'
           WHEN 's' THEN 'STABLE'
           WHEN 'v' THEN 'VOLATILE'
       END AS volatility
FROM pg_proc
WHERE proname = 'age_in_years';