CREATE FUNCTION trong PostgreSQL

Tình huống mở đầu

Cà Phê Chú Tư có một bảng cafe_orders với 4 dòng hóa đơn. Tối nào Chú Tư cũng cộng thuế VAT 10% vào giá từng món, và công thức round(price * 1.10, 0) đã nằm rải rác ở 3 chỗ: báo cáo, màn hình thu ngân và file xuất kế toán. Tháng này thuế đổi sang 8%. Chú sửa hai chỗ, quên chỗ thứ ba, và cuối tháng báo cáo lệch con số với sổ kế toán đúng 900 đồng cho mỗi ly latte.

Hãy đoán trước: nếu công thức chỉ nằm ở đúng một chỗ trong database, Chú Tư phải sửa mấy nơi? Và nếu chỗ đó là một hàm do chính Chú viết, PostgreSQL cần biết thêm những gì về hàm để dùng nó an toàn trong WHERE và trong chỉ mục?

Ôn nhanh bài trước: trong WHERE EXISTS (SELECT ... ), viết SELECT 1 hay SELECT * bên trong có khác nhau không?

Đáp án: không khác về kết quả, vì EXISTS chỉ hỏi có ít nhất một dòng hay không. Bài này chuyển sang một chủ đề mới: tự viết hàm.

Mục tiêu bài học

Sau bài này bạn có thể:

• Viết CREATE FUNCTION bằng cả LANGUAGE sql và LANGUAGE plpgsql, và nói được vì sao thân hàm nằm trong dấu đô la kép.
• Chọn đúng nhãn IMMUTABLE, STABLE hay VOLATILE và dự đoán ảnh hưởng của nó lên số lần gọi hàm và lên chỉ mục biểu thức.
• Giải thích STRICT làm gì với đầu vào NULL, và CREATE OR REPLACE không được đổi những gì.
• Phân biệt hàm với PROCEDURE: gọi bằng SELECT hay CALL.

Bức tranh lớn

Xuyên suốt bài, một hàm là một thẻ công thức treo trong bếp của Cà Phê Chú Tư: có tên thẻ, nguyên liệu cần đưa vào (tham số), món làm ra (kiểu trả về) và các bước làm (thân hàm). Dự án xuyên suốt là bảng hóa đơn cafe_orders trong database shop:

CREATE TABLE cafe_orders (
  order_id int PRIMARY KEY,
  item text NOT NULL,
  qty int NOT NULL,
  unit_price numeric(12,2) NOT NULL
);
INSERT INTO cafe_orders VALUES
  (1, 'latte', 2, 45000),
  (2, 'americano', 1, 35000),
  (3, 'peach tea', 3, 40000),
  (4, 'cake', 1, 50000);

Tên hàm trong bài đều bắt đầu bằng demo_ để không trùng với hàm có sẵn của PostgreSQL. Hình dưới trả lời một câu hỏi: từ lúc gõ CREATE FUNCTION đến lúc gọi hàm, PostgreSQL đi qua những chặng nào?

Cách đọc hình: đọc từ ô xanh dương ở trên cùng. Ô cam hỏi hàm viết bằng ngôn ngữ nào. Nhánh sql có thân là các câu lệnh SQL thường, nhánh plpgsql có thân là một khối lệnh có IF và RETURN. Cả hai nhánh cùng dẫn vào ô xanh lơ: định nghĩa được lưu trong catalog pg_proc. Ô xanh lá cuối cùng là lúc bạn gọi hàm bằng SELECT.

Chỗ ẩn dụ không còn đúng: thẻ công thức thật không có nhãn nào nói "cùng nguyên liệu thì luôn ra cùng món". Hàm trong PostgreSQL thì có nhãn đó, và planner dựa vào nó để đi đường tắt.

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. Hàm gồm tên, tham số, kiểu trả về và thân hàm

CREATE FUNCTION tên(tham_số kiểu, ...) RETURNS kiểu LANGUAGE ngôn_ngữ AS $$ thân hàm $$. Thân hàm là một chuỗi ký tự. Dấu $$ (gọi là dollar quoting) bao chuỗi đó để bạn không phải escape dấu nháy đơn bên trong. Nếu thân hàm lại chứa $$, đặt tên cho cặp dấu, ví dụ $body$ ... $body$. Với LANGUAGE sql, giá trị trả về là kết quả của câu lệnh cuối. Với LANGUAGE plpgsql, bạn viết khối BEGIN ... END và phải tự RETURN.

2. Nhãn biến động: IMMUTABLE, STABLE, VOLATILE

Đây là lời hứa của bạn với planner. IMMUTABLE: cùng đối số luôn cho cùng kết quả, không đọc bảng, không phụ thuộc cấu hình phiên. STABLE: trong một câu lệnh thì cùng đối số cho cùng kết quả, được phép đọc bảng. VOLATILE (mặc định): có thể trả khác nhau ở mỗi lần gọi hoặc có tác dụng phụ. Nhãn quyết định planner có tính trước, có gom lời gọi, và có cho dùng hàm trong chỉ mục biểu thức hay không.

3. STRICT và đầu vào NULL

Hàm gắn STRICT (viết đầy đủ là RETURNS NULL ON NULL INPUT) trả NULL ngay khi có bất kỳ đối số nào là NULL, mà không chạy thân hàm. Không gắn STRICT thì thân hàm vẫn chạy với NULL, và bạn tự quyết định cách xử lý.

4. CREATE OR REPLACE và hàm khác PROCEDURE

CREATE OR REPLACE FUNCTION thay thân hàm có cùng chữ ký (tên cùng danh sách kiểu tham số) mà không phải xóa hàm cũ, nhưng không cho đổi kiểu trả về hay tên tham số. PROCEDURE là loại đối tượng khác: không trả giá trị qua SELECT, gọi bằng CALL.

Ví dụ đầu tiên

Ví dụ nhỏ nhất: đưa công thức VAT vào một hàm và gọi nó.

CREATE FUNCTION demo_with_vat(price numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT round(price * 1.10, 0)
$$;

SELECT demo_with_vat(45000) AS latte_with_vat;

Dự đoán trước khi xem kết quả: giá latte là 45000, sau VAT 10% là bao nhiêu?

Kết quả [Đã chạy: PostgreSQL 16.15]:

CREATE FUNCTION
 latte_with_vat 
----------------
          49500
(1 row)

Hàm dùng được trên từng dòng của bảng, ngay trong danh sách cột [Đã chạy: PostgreSQL 16.15]:

SELECT item, unit_price, demo_with_vat(unit_price) AS with_vat
FROM cafe_orders
ORDER BY order_id;

   item    | unit_price | with_vat 
-----------+------------+----------
 latte     |   45000.00 |    49500
 americano |   35000.00 |    38500
 peach tea |   40000.00 |    44000
 cake      |   50000.00 |    55000
(4 rows)

Công thức giờ chỉ nằm ở một chỗ. Khi thuế đổi, Chú Tư sửa đúng một hàm. Với logic có rẽ nhánh thì dùng plpgsql. Hàm dưới gắn nhãn theo số ly trong mỗi dòng hóa đơn [Đã chạy: PostgreSQL 16.15]:

CREATE FUNCTION demo_label(qty int)
RETURNS text
LANGUAGE plpgsql
AS $body$
BEGIN
  IF qty >= 3 THEN
    RETURN 'party';
  ELSIF qty = 2 THEN
    RETURN 'pair';
  END IF;
  RETURN 'single';
END;
$body$;

SELECT order_id, qty, demo_label(qty) AS label
FROM cafe_orders
ORDER BY order_id;

CREATE FUNCTION
 order_id | qty | label  
----------+-----+--------
        1 |   2 | pair
        2 |   1 | single
        3 |   3 | party
        4 |   1 | single
(4 rows)

Nhãn biến động đổi số lần gọi hàm

Ba hàm dưới có cùng thân, chỉ khác nhãn. Mỗi lần thân hàm chạy, nó cộng một vào biến phiên demo.calls để ta đếm được. Đây là bản IMMUTABLE, hai bản kia đổi tên hàm và nhãn:

CREATE FUNCTION demo_tax_immutable(price numeric)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  PERFORM set_config('demo.calls', (coalesce(nullif(current_setting('demo.calls', true), ''), '0')::int + 1)::text, false);
  RETURN round(price * 1.10, 0);
END;
$$;

Câu truy vấn đếm các dòng có unit_price nhỏ hơn giá sau thuế của 40000, rồi đọc lại bộ đếm. Kết quả [Đã chạy: PostgreSQL 16.15, bảng 4 dòng]:

SELECT set_config('demo.calls', '0', false) AS reset \gset
SELECT count(*) AS rows_found FROM cafe_orders WHERE unit_price < demo_tax_immutable(40000);
SELECT current_setting('demo.calls') AS immutable_calls;

SELECT set_config('demo.calls', '0', false) AS reset \gset
SELECT count(*) AS rows_found FROM cafe_orders WHERE unit_price < demo_tax_volatile(40000);
SELECT current_setting('demo.calls') AS volatile_calls;

SELECT set_config('demo.calls', '0', false) AS reset \gset
SELECT count(*) AS rows_found FROM cafe_orders WHERE unit_price < demo_tax_stable(40000);
SELECT current_setting('demo.calls') AS stable_calls;

 rows_found 
------------
          2
(1 row)

 immutable_calls 
-----------------
 1
(1 row)

 rows_found 
------------
          2
(1 row)

 volatile_calls 
----------------
 4
(1 row)

 rows_found 
------------
          2
(1 row)

 stable_calls 
--------------
 5
(1 row)

Hàm IMMUTABLE chạy 1 lần, VOLATILE chạy 4 lần (một lần cho mỗi dòng), STABLE chạy 5 lần: 4 lần theo dòng cộng 1 lần planner chạy thử để ước lượng độ chọn lọc [Có thể khác nhau: tùy phiên bản và kế hoạch]. Cả ba cho cùng kết quả 2 dòng, nên nhãn không đổi kết quả, chỉ đổi cách máy làm việc.

Kế hoạch cho thấy hàm IMMUTABLE đã biến mất khỏi điều kiện lọc [Có thể khác nhau: kế hoạch tùy phiên bản]:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT item FROM cafe_orders WHERE unit_price < demo_tax_immutable(40000);

                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on public.cafe_orders
   Output: item
   Filter: (cafe_orders.unit_price < '44000'::numeric)
(3 rows)

Điều kiện lọc là unit_price < '44000'::numeric: lời gọi hàm được thay bằng kết quả đã tính.

Xem từng bước

Animation dưới đây chạy lại đúng thí nghiệm trên một bảng 4 dòng. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: PostgreSQL 16.15, bảng cafe_orders 4 dòng, điều kiện unit_price < f(40000)]:

Bạn vừa làm được: nhìn nhãn của một hàm và nói trước hàm sẽ chạy một lần hay theo từng dòng.

Concept Check

Dùng các hàm của bài: demo_with_vat (IMMUTABLE), demo_label (plpgsql, không STRICT).

Câu 1. Thân hàm demo_label có RETURN 'single' ở cuối. Nếu xóa dòng đó và gọi demo_label(0), chuyện gì xảy ra?

• A. Hàm trả NULL, vì không nhánh nào khớp nên không có giá trị nào để trả.
• B. Hàm không tạo được, vì PostgreSQL kiểm tra mọi đường đi lúc CREATE.
• C. Hàm trả chuỗi rỗng, vì kiểu text có giá trị mặc định là rỗng.
• D. Hàm tạo được nhưng khi gọi báo lỗi control reached end of function without RETURN.

Câu 2. Vì sao hàm demo_with_vat hợp lệ để gắn nhãn IMMUTABLE?

• A. Vì hàm viết bằng LANGUAGE sql nên PostgreSQL tự coi nó là IMMUTABLE.
• B. Vì kết quả chỉ phụ thuộc đối số, không đọc bảng và không phụ thuộc phiên.
• C. Vì hàm chỉ có một dòng trong thân nên không thể có tác dụng phụ.
• D. Vì tên hàm bắt đầu bằng demo_ nên PostgreSQL bỏ qua kiểm tra nhãn.

Câu 3. Hàm STRICT được gọi với đối số NULL thì thân hàm có chạy không?

• A. Có, thân hàm chạy bình thường rồi kết quả được đổi thành NULL.
• B. Có, nhưng chỉ khi tất cả đối số đều là NULL chứ không phải một đối số.
• C. Không, kết quả là NULL ngay mà thân hàm không chạy lần nào.
• D. Không, và PostgreSQL báo lỗi vì hàm STRICT không nhận NULL.

Đáp án và giải thích:

• Câu 1: D. Với plpgsql, PostgreSQL chỉ kiểm tra cú pháp lúc tạo, còn việc chạm RETURN được kiểm tra khi chạy, nên lỗi 2F005 xuất hiện lúc gọi. A, B và C đều sai.
• Câu 2: B. IMMUTABLE là lời hứa về hành vi, không phải tính chất của ngôn ngữ hay độ dài thân hàm. A, C và D đều không phải là điều kiện.
• Câu 3: C. STRICT làm PostgreSQL bỏ qua thân hàm nếu bất kỳ đối số nào là NULL. A mô tả sai cơ chế, B sai vì một đối số NULL cũng đủ, D sai vì NULL vẫn được nhận.

Làm cùng mình

Chú Tư muốn tính tiền boa gợi ý: hóa đơn từ 100000 trở lên thì gợi ý boa 5% (làm tròn đến đồng), dưới đó thì không gợi ý. Mỗi dòng của cafe_orders có qty * unit_price là tổng tiền của dòng.

Bước 1. Vẽ ra giấy

Tính tổng của từng dòng: latte 90000, americano 35000, peach tea 120000, cake 50000. Ghi dự đoán tiền boa gợi ý cho từng dòng.

Bước 2. Điền chỗ trống

CREATE FUNCTION demo_tip(amount numeric)
RETURNS numeric        -- TODO 1: kiểu trả về là kiểu gì cho khớp với tiền
LANGUAGE sql
...                    -- TODO 2: nhãn biến động phù hợp, hàm chỉ dùng phép tính trên đối số
AS $$
  SELECT CASE WHEN amount >= 100000 THEN ... ELSE 0 END   -- TODO 3: tính 5% của amount rồi làm tròn
$$;

Gợi ý: kiểu trả về giống kiểu của amount. Nhãn nằm trong ba từ đã học. Công thức làm tròn giống demo_with_vat.

Bước 3. Chạy và đối chiếu

Chạy xong bạn sẽ thấy [Đã chạy] bốn dòng, chỉ có dòng peach tea khác 0. Đáp án ở mục Lời giải và đáp án.

Bước 4. Thêm STRICT

Tạo thêm một bản demo_tip_strict giống hệt nhưng có STRICT, rồi gọi cả hai hàm với NULL. Dự đoán trước: hai hàm trả gì? Sau đó so với kết quả ở mục Lời giải và đáp án.

Thử phá nó đi

Năm lỗi người mới thường gặp với CREATE FUNCTION [Đã chạy: PostgreSQL 16.15]:

Lỗi thứ nhất [Đã chạy: PostgreSQL 16.15]:

CREATE FUNCTION demo_nodollar(price numeric)
RETURNS numeric
LANGUAGE sql
AS SELECT price * 2;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42601
ERROR:  syntax error at or near "SELECT"
LINE 4: AS SELECT price * 2;
           ^

Lỗi thứ hai, kiểu trả về không khớp:

CREATE FUNCTION demo_bad_type(qty int)
RETURNS int
LANGUAGE sql
AS $$ SELECT 'many'::text $$;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42P13
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Actual return type is text.
CONTEXT:  SQL function "demo_bad_type"

Lỗi thứ ba có hai bước: hàm tạo được, gọi với qty bằng 1 thì chạy, gọi với 0 thì lỗi. Dòng ERROR do psql in ở cuối vì nằm trên luồng lỗi:

SELECT demo_no_return(1) AS ok_call;
SELECT demo_no_return(0) AS bad_call;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

 ok_call 
---------
       1
(1 row)

SQLSTATE: 2F005
ERROR:  relation "cafe_orders" already exists
ERROR:  duplicate key value violates unique constraint "cafe_orders_pkey"
DETAIL:  Key (order_id)=(1) already exists.
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function demo_no_return(integer)

Lỗi thứ tư là lỗi im lặng nguy hiểm nhất. Tham số tên item trùng tên cột item, và điều kiện item = item luôn đúng với mọi dòng không NULL:

CREATE FUNCTION demo_find(item text)
RETURNS bigint
LANGUAGE sql
AS $$ SELECT count(*) FROM cafe_orders WHERE item = item $$;

SELECT demo_find('latte') AS found;

CREATE FUNCTION
 found 
-------
     4
(1 row)

Đáng ra phải ra 1 dòng latte, nhưng hàm đếm cả 4 dòng. Đổi tên tham số thành p_item thì đúng:

CREATE FUNCTION demo_find(p_item text)
RETURNS bigint
LANGUAGE sql
AS $$ SELECT count(*) FROM cafe_orders WHERE item = p_item $$;

SELECT demo_find('latte') AS found;

CREATE FUNCTION
 found 
-------
     1
(1 row)

Lỗi thứ năm, đổi kiểu trả về và đổi tên tham số bằng CREATE OR REPLACE:

CREATE OR REPLACE FUNCTION demo_with_vat(price numeric)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$ SELECT round(price * 1.10, 0)::text $$;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42P13
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION demo_with_vat(numeric) first.

CREATE OR REPLACE FUNCTION demo_with_vat(amount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$ SELECT round(amount * 1.10, 0) $$;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42P13
ERROR:  cannot change name of input parameter "price"
HINT:  Use DROP FUNCTION demo_with_vat(numeric) first.

Cả hai đều bị từ chối bằng mã 42P13 và gợi ý DROP FUNCTION trước. Nhưng đổi thân hàm với cùng chữ ký thì được, và các lời gọi sau đó dùng công thức mới:

SELECT demo_with_vat(45000) AS before_change;

CREATE OR REPLACE FUNCTION demo_with_vat(price numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$ SELECT round(price * 1.08, 0) $$;

SELECT demo_with_vat(45000) AS after_change;

 before_change 
---------------
         49500
(1 row)

CREATE FUNCTION
 after_change 
--------------
        48600
(1 row)

Nhãn sai làm chỉ mục nói dối

Hiểu lầm quan trọng nhất của bài là coi IMMUTABLE chỉ là gợi ý cho nhanh. Trước hết, PostgreSQL chặn nhãn không đủ mạnh khi hàm đi vào chỉ mục biểu thức. Hàm STABLE bị từ chối [Đã chạy: PostgreSQL 16.15]:

CREATE FUNCTION demo_lower_stable(t text)
RETURNS text
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN lower(t);
END;
$$;

CREATE INDEX cafe_orders_item_idx ON cafe_orders (demo_lower_stable(item));
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

CREATE FUNCTION
SQLSTATE: 42P17
ERROR:  functions in index expression must be marked IMMUTABLE

Còn hàm IMMUTABLE thì lập được chỉ mục và truy vấn dùng nó [Có thể khác nhau: kế hoạch tùy thống kê]:

CREATE FUNCTION demo_lower(t text) RETURNS text LANGUAGE sql IMMUTABLE AS $$ SELECT lower(t) $$;
INSERT INTO cafe_orders SELECT g, 'Item ' || g, 1, 30000 FROM generate_series(5, 20000) AS g;
CREATE INDEX cafe_orders_item_idx ON cafe_orders (demo_lower(item));
ANALYZE cafe_orders;
EXPLAIN (COSTS OFF)
SELECT order_id FROM cafe_orders WHERE demo_lower(item) = 'item 777';

CREATE FUNCTION
INSERT 0 19996
CREATE INDEX
ANALYZE
                      QUERY PLAN                      
------------------------------------------------------
 Index Scan using cafe_orders_item_idx on cafe_orders
   Index Cond: (lower(item) = 'item 777'::text)
(2 rows)

Nhưng PostgreSQL không kiểm tra thân hàm có thật sự bất biến không. Hàm dưới đọc thuế suất từ bảng vat_rate nhưng bị gắn IMMUTABLE, và một chỉ mục được lập trên nó. Sau khi thuế đổi từ 10% xuống 8%, truy vấn dùng chỉ mục trả kết quả theo thuế cũ [Đã chạy: PostgreSQL 16.15, 20000 dòng]:

UPDATE vat_rate SET rate = 0.08;

SELECT demo_vat_of(unit_price) AS vat_now FROM cafe_orders WHERE order_id = 100;

EXPLAIN (COSTS OFF)
SELECT order_id FROM cafe_orders WHERE demo_vat_of(unit_price) = 3010;

SELECT count(*) AS found_with_old_rate FROM cafe_orders WHERE demo_vat_of(unit_price) = 3010;
SELECT count(*) AS found_with_new_rate FROM cafe_orders WHERE demo_vat_of(unit_price) = 2408;

UPDATE 1
 vat_now 
---------
    2408
(1 row)

                        QUERY PLAN                         
-----------------------------------------------------------
 Index Scan using cafe_orders_vat_idx on cafe_orders
   Index Cond: (demo_vat_of(unit_price) = '3010'::numeric)
(2 rows)

 found_with_old_rate 
---------------------
                  10
(1 row)

 found_with_new_rate 
---------------------
                   0
(1 row)

Giá trị hiện tại cho dòng 100 là 2408 (thuế 8%), nhưng chỉ mục vẫn tìm thấy dòng này bằng giá trị 3010 (thuế 10%) và không tìm thấy bằng 2408. Chỉ mục đã lưu giá trị cũ, vì ta hứa với PostgreSQL rằng hàm không bao giờ đổi kết quả. Hình dưới đối chiếu mô hình sai với mô hình đúng.

Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai, đi từ coi nhãn là gợi ý, gắn IMMUTABLE cho hàm đọc bảng, đến chỉ mục chứa giá trị cũ. Chuỗi xanh là thực tế: nhãn là lời hứa mà planner tin, nên hàm đọc bảng chỉ được gắn STABLE hoặc VOLATILE.

Tự tay làm

Dùng chung bảng cafe_orders và các hàm trong bài.

Cơ bản

Bài 1. Dự đoán nhãn. Đầu vào: hàm demo_label ở trên (không STRICT, không rẽ nhánh nào cho NULL). Điền kết quả của demo_label(0), demo_label(2) và demo_label(NULL) trước khi chạy. Tiêu chí đạt: đúng cả ba giá trị. Gợi ý: NULL >= 3 và NULL = 2 cho kết quả NULL, và IF coi NULL là không đúng.

Vận dụng

Bài 2. Tổng hóa đơn. Đầu vào: bảng cafe_orders. Viết hàm demo_line_total(qty int, unit_price numeric) trả qty * unit_price, rồi dùng nó trong sum để ra tổng cả hóa đơn. Tiêu chí đạt: tổng bằng 295000. Gợi ý: hàm chỉ tính trên đối số nên chọn nhãn IMMUTABLE, và hãy để ý tham số trùng tên cột.

Thử thách (tùy chọn)

Bài 3. Sửa hàm đếm sai. Đầu vào: hàm demo_count_item(item text) đếm các dòng có item = item và luôn trả 4. Sửa mà không đổi tên tham số (người gọi có thể đang dùng tham số có tên), rồi thử đổi kiểu trả về của hàm từ bigint sang int bằng CREATE OR REPLACE và đọc lỗi. Tiêu chí đạt: demo_count_item('cake') trả 1, và bạn nêu được mã lỗi của lần đổi kiểu. Gợi ý: trong thân hàm, tên tham số có thể viết kèm tên hàm làm tiền tố.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, nhớ lại) Dollar quoting dùng để làm gì, và khi nào cần viết $body$ thay cho $$?
2. (Mục tiêu 2, dự đoán) Hàm đọc bảng vat_rate nên gắn nhãn nào, và vì sao không gắn IMMUTABLE?
3. (Mục tiêu 2, dự đoán) Bảng 1000 dòng, điều kiện price < f(40000) với f là VOLATILE. Thân hàm chạy khoảng bao nhiêu lần?
4. (Mục tiêu 3, giải thích) STRICT và việc dùng coalesce trong thân hàm khác nhau thế nào khi đối số là NULL?
5. (Mục tiêu 4, tìm lỗi) CREATE OR REPLACE FUNCTION f(x int) RETURNS text ... báo lỗi vì hàm f(int) cũ trả int. Cách xử lý đúng là gì?

Điều cần nhớ

• Hàm gồm tên, tham số, RETURNS, LANGUAGE và thân bao bằng dollar quoting.
• Nhãn IMMUTABLE, STABLE, VOLATILE là lời hứa với planner và quyết định số lần gọi hàm lẫn khả năng dùng trong chỉ mục.
• STRICT trả NULL mà không chạy thân hàm khi có đối số NULL.
• CREATE OR REPLACE không đổi được kiểu trả về hay tên tham số; hàm plpgsql chỉ bị kiểm tra đường RETURN khi chạy.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho ngày mai: Chú Tư muốn thêm hàm demo_with_vat(price numeric, rate numeric) cạnh hàm một tham số hiện có, cùng tên. PostgreSQL cho phép hai hàm trùng tên cùng tồn tại không, và khi gọi nó chọn hàm nào?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Mỗi hàm là một dòng trong catalog pg_proc. Truy vấn dưới đây cho thấy ngôn ngữ, nhãn biến động, STRICT và SECURITY DEFINER của ba hàm mẫu [Đã chạy: PostgreSQL 16.15]:

CREATE FUNCTION demo_a(x int) RETURNS int LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT x + 1 $$;
CREATE FUNCTION demo_b(x int) RETURNS int LANGUAGE plpgsql AS $$ BEGIN RETURN x + 1; END $$;
CREATE FUNCTION demo_c(x int)
RETURNS int
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$ SELECT x + 1 $$;

SELECT proname,
       (SELECT lanname FROM pg_language WHERE oid = prolang) AS lang,
       CASE provolatile WHEN 'i' THEN 'immutable' WHEN 's' THEN 'stable' ELSE 'volatile' END AS volatility,
       proisstrict AS strict,
       prosecdef AS definer,
       proconfig
FROM pg_proc
WHERE proname LIKE 'demo\_%'
ORDER BY proname;

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
 proname |  lang   | volatility | strict | definer |             proconfig              
---------+---------+------------+--------+---------+------------------------------------
 demo_a  | sql     | immutable  | t      | f       | NULL
 demo_b  | plpgsql | volatile   | f      | f       | NULL
 demo_c  | sql     | stable     | f      | t       | {"search_path=pg_catalog, public"}
(3 rows)

Hàm demo_c có SECURITY DEFINER: nó chạy với quyền của người tạo hàm chứ không phải người gọi, nên phải ghim search_path như trên để người gọi không chen được một bảng giả vào đường tìm kiếm. [Suy luận: theo tài liệu PostgreSQL 16, trang của lệnh CREATE FUNCTION, bài này chưa chạy thử tấn công].

• So sánh với lựa chọn khác. Hàm có thể trả nhiều dòng bằng RETURNS TABLE, nhiều cột bằng tham số OUT, nhận số lượng đối số tùy ý bằng VARIADIC và có đối số mặc định [Đã chạy: PostgreSQL 16.15]:

CREATE FUNCTION demo_top(n int DEFAULT 2)
RETURNS TABLE (item text, line_total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT item, qty * unit_price FROM cafe_orders ORDER BY qty * unit_price DESC LIMIT n
$$;

CREATE FUNCTION demo_split(total numeric, OUT net numeric, OUT vat numeric)
LANGUAGE sql
IMMUTABLE
AS $$ SELECT round(total / 1.10, 0), total - round(total / 1.10, 0) $$;

CREATE FUNCTION demo_sum_all(VARIADIC nums numeric[])
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$ SELECT sum(x) FROM unnest(nums) AS x $$;

SELECT * FROM demo_top();
SELECT * FROM demo_top(3);
SELECT * FROM demo_split(110000);
SELECT demo_sum_all(10, 20, 30) AS total;

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
   item    | line_total 
-----------+------------
 peach tea |  120000.00
 latte     |   90000.00
(2 rows)

   item    | line_total 
-----------+------------
 peach tea |  120000.00
 latte     |   90000.00
 cake      |   50000.00
(3 rows)

  net   |  vat  
--------+-------
 100000 | 10000
(1 row)

 total 
-------
    60
(1 row)

PROCEDURE khác hàm ở chỗ gọi bằng CALL và (từ PostgreSQL 11) được phép kết thúc giao dịch bên trong thân. Ví dụ dưới giảm tồn kho bằng một procedure [Đã chạy: PostgreSQL 16.15]:

CREATE TABLE stock (item text PRIMARY KEY, qty int NOT NULL);
INSERT INTO stock VALUES ('latte', 10);

CREATE PROCEDURE demo_restock(p_item text, p_add int)
LANGUAGE sql
AS $$ UPDATE stock SET qty = qty + p_add WHERE item = p_item $$;

CALL demo_restock('latte', 5);
SELECT * FROM stock;

CREATE TABLE
INSERT 0 1
CREATE PROCEDURE
CALL
 item  | qty 
-------+-----
 latte |  15
(1 row)

Gọi procedure bằng SELECT bị từ chối:

SELECT demo_restock('latte', 5);
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

SQLSTATE: 42809
ERROR:  relation "cafe_orders" already exists
ERROR:  duplicate key value violates unique constraint "cafe_orders_pkey"
DETAIL:  Key (order_id)=(1) already exists.
ERROR:  demo_restock(unknown, integer) is a procedure
LINE 1: SELECT demo_restock('latte', 5);
               ^
HINT:  To call a procedure, use CALL.

• Hiểu lầm nâng cao. Hàm LANGUAGE sql đơn giản được inline vào câu truy vấn, nên trong kế hoạch ở trên chỉ mục demo_lower(item) lại hiện lower(item). Hàm plpgsql thì không bao giờ được inline, mỗi lần gọi là một lần vào bộ thông dịch plpgsql.
• Giới hạn của bài. Bài này chưa nói đến ghi đè hàm trùng tên với tham số khác kiểu (bài sau), việc xóa hàm (bài kế tiếp), trigger function, và hàm viết bằng ngôn ngữ khác như Python.
• Đọc thêm. Tài liệu PostgreSQL 16, trang của lệnh CREATE FUNCTION: https://www.postgresql.org/docs/16/sql-createfunction.html và mục Function Volatility Categories: https://www.postgresql.org/docs/16/xfunc-volatility.html

Bài tiếp theo

Bài sau là Function Overloading: nhiều hàm cùng tên khác kiểu tham số, và cách PostgreSQL chọn hàm khi lời gọi mơ hồ. Mini-dự án nối tiếp: viết demo_with_vat(price, rate) cạnh bản một tham số và xem PostgreSQL chọn bản nào cho demo_with_vat(45000).

Lời giải và đáp án

Làm cùng mình, Bước 2. Ba chỗ trống là numeric, IMMUTABLE và round(amount * 0.05, 0):

CREATE FUNCTION demo_tip(amount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE WHEN amount >= 100000 THEN round(amount * 0.05, 0) ELSE 0 END
$$;

SELECT item, qty * unit_price AS line_total, demo_tip(qty * unit_price) AS tip
FROM cafe_orders
ORDER BY order_id;

CREATE FUNCTION
   item    | line_total | tip  
-----------+------------+------
 latte     |   90000.00 |    0
 americano |   35000.00 |    0
 peach tea |  120000.00 | 6000
 cake      |   50000.00 |    0
(4 rows)

Làm cùng mình, Bước 4.

CREATE FUNCTION demo_tip(amount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE WHEN amount >= 100000 THEN round(amount * 0.05, 0) ELSE 0 END
$$;

CREATE FUNCTION demo_tip_strict(amount numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $$
  SELECT CASE WHEN amount >= 100000 THEN round(amount * 0.05, 0) ELSE 0 END
$$;

SELECT demo_tip(NULL) AS plain, demo_tip_strict(NULL) AS strict_version;

CREATE FUNCTION
CREATE FUNCTION
 plain | strict_version 
-------+----------------
     0 |           NULL
(1 row)

Hàm không STRICT chạy thân hàm với NULL: NULL >= 100000 không đúng nên CASE rơi vào ELSE 0 và trả 0. Bản STRICT trả NULL mà không chạy thân hàm. Cả hai đều hợp lý, nhưng khác nhau, nên phải chọn có chủ đích.

Bài 1.

SELECT demo_label(0) AS l0, demo_label(2) AS l2, demo_label(NULL) AS l_null;

   l0   |  l2  | l_null 
--------+------+--------
 single | pair | single
(1 row)

ERROR:  relation "cafe_orders" already exists
ERROR:  duplicate key value violates unique constraint "cafe_orders_pkey"
DETAIL:  Key (order_id)=(1) already exists.

demo_label(0) là single (không nhánh nào khớp, rơi xuống RETURN cuối), demo_label(2) là pair, demo_label(NULL) cũng là single vì mọi phép so sánh với NULL đều không đúng nên hàm đi hết các nhánh.

Bài 2.

CREATE FUNCTION demo_line_total(qty int, unit_price numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$ SELECT qty * unit_price $$;

SELECT sum(demo_line_total(qty, unit_price)) AS bill_total FROM cafe_orders;

CREATE FUNCTION
 bill_total 
------------
  295000.00
(1 row)

Bài 3. Bản lỗi và bản sửa bằng tiền tố là tên hàm:

CREATE FUNCTION demo_count_item(item text)
RETURNS bigint
LANGUAGE sql
AS $$ SELECT count(*) FROM cafe_orders WHERE item = item $$;

SELECT demo_count_item('cake') AS found;

CREATE FUNCTION
 found 
-------
     4
(1 row)

CREATE FUNCTION demo_count_item(item text)
RETURNS bigint
LANGUAGE sql
AS $$ SELECT count(*) FROM cafe_orders AS o WHERE o.item = demo_count_item.item $$;

SELECT demo_count_item('cake') AS found;

CREATE FUNCTION
 found 
-------
     1
(1 row)

Đổi kiểu trả về bằng CREATE OR REPLACE:

CREATE FUNCTION demo_count_item(item text) RETURNS bigint LANGUAGE sql AS $$ SELECT 0::bigint $$;
CREATE OR REPLACE FUNCTION demo_count_item(item text) RETURNS int LANGUAGE sql AS $$ SELECT 0 $$;
\echo SQLSTATE: :LAST_ERROR_SQLSTATE

CREATE FUNCTION
SQLSTATE: 42P13
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION demo_count_item(text) first.

Lỗi mã 42P13, và lời gợi ý là phải DROP FUNCTION trước.

Tự kiểm tra.

1. Dollar quoting bao thân hàm để không phải escape dấu nháy đơn. Cần $body$ khi thân hàm chứa $$.
2. STABLE (hoặc VOLATILE). Gắn IMMUTABLE thì chỉ mục và việc tính trước lưu giá trị cũ sau khi bảng đổi.
3. Khoảng 1000 lần, mỗi dòng một lần.
4. STRICT bỏ qua thân hàm và trả NULL. coalesce trong thân hàm cho bạn thay NULL bằng giá trị thay thế, nhưng chỉ khi thân hàm thật sự chạy (không STRICT).
5. Xóa hàm cũ bằng DROP FUNCTION f(int) rồi tạo lại với kiểu mới, hoặc tạo hàm tên khác, vì CREATE OR REPLACE không đổi được kiểu trả về.