Menu

CREATE FUNCTION và stored procedure trong PostgreSQL

CREATE FUNCTION định nghĩa một hàm bạn gọi bên trong truy vấn, viết bằng SQL hoặc PL/pgSQL. CREATE PROCEDURE định nghĩa một thủ tục chạy bằng CALL, có thể commit transaction giữa chừng.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

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àmProcedure
Chạy bằngSELECT f(...), ở bất kỳ đâu trong truy vấnchỉ CALL p(...)
Trả vềmột giá trị, một hàng hoặc một tập hàngkhông gì cả (hoặc tham số OUT/INOUT)
COMMIT / ROLLBACK bên trongkhôngcó, 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ùng random() 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);.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu