Gọi function từ SQL query

Hãy tưởng tượng bạn có một function để tính toán phức tạp hoặc xử lý dữ liệu. Nếu không thể tích hợp function vào SQL query, bạn sẽ phải làm như sau:

1. Gọi function từ một ngôn ngữ lập trình (ví dụ Python hoặc JavaScript).
2. Truyền kết quả vào SQL query.

Đó là một bước thừa! Trong PostgreSQL, bạn có thể nhúng function trực tiếp vào SQL query, giảm code, tăng tốc thao tác và giảm số lần gọi server. Điều này đặc biệt hữu ích cho:

• Tự động hóa tính toán.
• Kiểm tra dữ liệu trước khi insert.
• Chỉnh sửa dữ liệu đã có.

Gọi function trong SELECT

Bắt đầu với cơ bản và xem cách dùng function trong query SELECT thông thường. Giả sử bạn có bảng students chứa thông tin sinh viên. Ta muốn viết function trả về tuổi hiện tại của sinh viên dựa trên ngày sinh.

Bước 1: viết function

Tạo function calculate_age, nhận ngày sinh và trả về tuổi:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Bước 2: dùng function trong query SELECT

Bây giờ bạn có thể gọi function này cho từng dòng trong bảng:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

Chuyện gì xảy ra?

• Với mỗi dòng trong bảng students, function calculate_age sẽ tính tuổi.
• Giá trị trả về sẽ hiển thị ở cột age.

Ví dụ kết quả:

Nhìn xem, không có gì khó, mà kết quả lại đẹp và chuyên nghiệp.

Gọi function trong INSERT

Function cũng rất hữu ích khi insert dữ liệu. Ví dụ, giả sử bạn có bảng logs ghi lại hành động của user. Bạn muốn insert log message bằng function tự động tạo text.

Bước 1: tạo function

Viết function generate_log_message, nhận tên user và hành động, trả về text message:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' performed action: ' || action || ' at ' || NOW();
END;
$$ LANGUAGE plpgsql;

Bước 2: dùng function trong INSERT

Bây giờ insert message vào bảng logs, gọi function khi thêm dòng:

INSERT INTO logs (message)
VALUES (generate_log_message('Otto', 'dang nhap vao trang web'));

Kết quả:

Function lo hết cho bạn: format text và thêm timestamp. Đây là ví dụ tuyệt vời cho việc function tự động hóa thao tác lặp lại.

Gọi function trong UPDATE

Function cũng có thể dùng để chỉnh sửa dữ liệu trong bảng. Giả sử bạn có bảng students và muốn update tên nhóm, dùng function để chuyển nhóm lên khóa mới.

Bước 1: tạo function

Viết function promote_student, nhận nhóm cũ (ví dụ 101) và trả về nhóm mới (ví dụ 201):

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

Bước 2: dùng function trong UPDATE

Cập nhật nhóm cho tất cả sinh viên:

UPDATE students
SET group_name = promote_student(group_name);

Kết quả:

Xem này, function được gọi đã làm phép biến đổi: nhóm cũ thành chuỗi mới.

Gọi function trong điều kiện WHERE

Function có thể dùng trong điều kiện lọc. Hãy mở rộng ví dụ về tuổi sinh viên.

Bước 1: lọc theo tuổi

Dùng function calculate_age đã tạo để chọn sinh viên trên 20 tuổi:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

Kết quả:

Ở đây, function chịu trách nhiệm tính tuổi cho từng sinh viên ngay khi query chạy.

Kết hợp với aggregate function

Hãy làm khó hơn chút. Ta cần đếm tổng số sinh viên dưới 22 tuổi. Function kết hợp rất tốt với aggregate như COUNT().

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

Chuyện gì xảy ra?

• Function calculate_age dùng để lọc.
• COUNT(*) đếm số dòng thỏa điều kiện.

Ví dụ thực tế

Tự động kiểm tra dữ liệu. Giả sử bạn muốn kiểm tra tuổi tất cả sinh viên trong bảng nằm trong khoảng hợp lý (ví dụ từ 18 đến 30 tuổi). Viết function kiểm tra và dùng nó trong điều kiện WHERE.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

Tối ưu hóa insert dữ liệu. Giả sử bạn làm việc ở shop online. Thay vì tính tổng giá trị đơn hàng ở phía client, hãy viết function tính trực tiếp khi insert vào bảng orders.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

Lỗi phổ biến khi gọi function

Khi bạn bắt đầu dùng function nhiều trong query, có thể gặp lỗi. Đây là vài tình huống thường gặp và cách xử lý:

Thiếu quyền cần thiết. Nếu bạn không phải owner của function hoặc bảng, PostgreSQL có thể không cho gọi. Hãy chắc chắn bạn có quyền thực thi function.

Sai kiểu dữ liệu. Khi truyền tham số vào function, chú ý kiểu dữ liệu. Ví dụ, nếu function cần DATE mà bạn truyền string thì sẽ lỗi. Hãy ép kiểu rõ ràng:

SELECT calculate_age('2000-01-01'::DATE);

Lỗi cú pháp trong function. Nếu function trả về lỗi, có thể làm hỏng cả query. Hãy test kỹ function trước khi dùng thật.