CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; định nghĩa một hàm mà truy vấn có thể gọi như hàm có sẵn. Thân hàm nằm giữa hai dấu $$, viết bằng SQL thuần hoặc PL/pgSQL, ngôn ngữ thủ tục của PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) thì định nghĩa một thủ tục (procedure) chạy bằng CALL.
with_tax(50) dùng thuế suất mặc định và trả về 60; with_tax(50, 0.1) trả về 55. Đối số cũng có thể truyền theo tên: with_tax(50, rate => 0.1).
Viết logic bằng PL/pgSQL
Dùng LANGUAGE plpgsql khi hàm cần biến, IF, vòng lặp hoặc xử lý lỗi. Biến nằm trong phần DECLARE, còn code nằm giữa BEGIN và END:
Một kiện hàng 4 kg tốn phí cơ bản 5; một kiện 20 kg gửi nhanh tốn (5 + 10 * 0.5) * 2 = 20. Bên trong PL/pgSQL, RAISE NOTICE 'value: %', cost; in ra một thông báo còn RAISE EXCEPTION dừng lại với một lỗi.
Khi SQL thuần đã diễn đạt được logic, hãy ưu tiên LANGUAGE sql: bộ lập kế hoạch có thể inline một hàm SQL đơn giản vào truy vấn gọi nó.
Trả về một tập hàng
RETURNS TABLE (...) tạo một hàm trả về các hàng. Gọi nó trong FROM như một bảng:
orders_of('ana') trả về đơn hàng 1 và 3. RETURNS SETOF orders trả về nguyên các hàng của bảng orders thay vì những cột được liệt kê.
Từ PostgreSQL 14, một hàm SQL có thể có thân viết bằng SQL chuẩn thay vì một chuỗi. PostgreSQL lưu nó ở dạng đã phân tích và ghi nhận các bảng và cột mà nó dùng, nên không thể xóa chúng khi hàm còn tồn tại:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Tạo stored procedure và chạy bằng CALL
Procedure không có kiểu trả về; bạn chạy nó bằng CALL. Nó có thể thay đổi dữ liệu, và khi CALL không nằm trong một khối transaction tường minh, nó có thể COMMIT giữa chừng. Đó là cách xử lý một tác vụ lớn theo từng đợt.
Tài khoản 1 còn 70 và tài khoản 2 có 80. Một lần chuyển 200 sẽ vi phạm ràng buộc CHECK trên tài khoản 1, và lỗi đó rollback cả hai lệnh cập nhật.
| Hàm | Procedure | |
|---|---|---|
| Chạy bằng | SELECT f(...), ở bất kỳ đâu trong truy vấn | chỉ CALL p(...) |
| Trả về | một giá trị, một hàng hoặc một tập hàng | không gì cả (hoặc tham số OUT/INOUT) |
COMMIT / ROLLBACK bên trong | không | có, trong PL/pgSQL, khi không được gọi bên trong khối transaction |
| Có từ | luôn có | PostgreSQL 11 |
Đánh dấu volatility: IMMUTABLE, STABLE, VOLATILE
Nhãn volatility cho bộ lập kế hoạch biết nó được phép giả định gì về một hàm. Mặc định là VOLATILE.
IMMUTABLE: kết quả chỉ phụ thuộc vào đối số và không bao giờ thay đổi (with_tax). Bắt buộc với những hàm dùng trong biểu thức index.STABLE: kết quả có thể đổi giữa các câu lệnh nhưng không đổi trong một câu lệnh, ví dụ một hàm đọc bảng (orders_of).VOLATILE: mọi trường hợp còn lại, như các hàm dùngrandom()hoặc thay đổi dữ liệu. PostgreSQL gọi lại nó cho từng hàng.
Gắn nhãn sai sẽ cho kết quả sai: một hàm IMMUTABLE đọc bảng có thể trả về giá trị cũ từ một index hoặc một kế hoạch đã được lưu đệm.
Thay thế, nạp chồng và xóa hàm
CREATE OR REPLACE FUNCTION thay đổi thân của một hàm đã có nhưng không đổi được kiểu trả về; muốn vậy phải xóa hàm trước. PostgreSQL nhận diện một hàm bằng tên và kiểu của các đối số, nên f(int) và f(text) là hai hàm khác nhau. Để xóa một trong hai hàm cùng tên, hãy ghi rõ kiểu đối số:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
Chương PL/pgSQL mô tả vòng lặp, cursor, ngoại lệ và SQL động với EXECUTE. Để chạy một hàm tự động khi các hàng thay đổi, hãy gắn nó vào một trigger.
Câu hỏi thường gặp
Hàm và procedure trong PostgreSQL khác nhau thế nào?
Hàm trả về một giá trị và được gọi bên trong truy vấn (SELECT f(x)). Procedure, có từ PostgreSQL 11, được chạy riêng bằng CALL và có thể COMMIT hoặc ROLLBACK bên trong thân của nó; hàm thì luôn chạy bên trong transaction của bên gọi.
$$ trong hàm PostgreSQL nghĩa là gì?
$$ là dollar quoting: nó đánh dấu chỗ bắt đầu và kết thúc của một literal chuỗi, nên thân hàm có thể chứa dấu nháy đơn mà không cần escape. Bạn cũng có thể thêm một tag, như $body$ ... $body$.
Làm sao xem code của một hàm PostgreSQL?
Trong psql, \df liệt kê các hàm và \sf name in ra định nghĩa của một hàm. Bằng SQL, SELECT pg_get_functiondef('name'::regproc); trả về đầy đủ câu lệnh CREATE OR REPLACE FUNCTION.
Hàm PostgreSQL có trả về nhiều hàng được không?
Được. Khai báo nó là RETURNS TABLE (col type, ...) hoặc RETURNS SETOF type rồi gọi nó trong mệnh đề FROM như một bảng: SELECT * FROM f(1);.