PostgreSQL : Giới thiệu hàm viết bằng ngôn ngữ truy vấn SQL

1. Hàm do người dùng định nghĩa

PostgreSQL cung cấp bốn loại hàm sau cho người dùng định nghĩa
- Hàm viết bằng ngôn ngữ truy vấn (query language functions : hàm viết bằng SQL)
- Hàm viết bằng ngôn ngữ thủ tục (procedural language functions : viết bằng PL/pgSQL, PL/Tcl, PL/Perl, PL/Python chẳng hạn)
- Hàm cung cấp sẵn (internal functions : là các hàm được viết bằng C được cung cấp sẵn trong server PostgreSQL)
- Hàm viết bằng ngôn ngữ C (C-language functions)

Ở đây tôi giới thiệu sơ về hàm viết bằng SQL

2. Giới thiệu hàm viết bằng ngôn ngữ truy vấn

Một hàm viết bằng SQL cho phép thực thi các câu truy vấn SQL định nghĩa trong hàm, và trả về kết quả của câu lệnh cuối cùng. Trường hợp đơn giản, hàm trả về dòng đầu tiên của kết quả câu truy vấn cuối cùng, nếu câu truy vấn này không trả về dòng kết quả nào thì hàm trả về giá trị null.

Muốn hàm trả về toàn bộ các dòng của câu truy vấn cuối, ta phải định nghĩa SETOF return_type hoặc TABLE(columns) trong đoạn RETURNS

Trong thân hàm, các câu lệnh phải được phân cách bằng dấu chấm phẩy, và không được sử dụng các lệnh transaction (như COMMIT, SAVEPOINT, ...). Một định nghĩa hàm đơn giản có cấu trúc như sau :

CREATE FUNCTION function_name (argument_name argument_type, argument_name argument_type, ...) 

RETURNS return_type AS $$
     function_body_query;
$$ language sql;

Thân hàm được viết dưới dạng hằng chuỗi (string constant), thông thường ta sử dụng dấu ngoặc đô-la (dollar quoting : $) để bao hằng chuỗi này. Dấu ngoặc đô-la không phải là một chuẩn của SQL.

Một hằng chuỗi bao bởi dấu ngoặc đô-la có dạng sau $[tag]$<string constant>$[tag]$ tức dấu đô-la + một tag (có thể có hoặc không) bao gồm không hay nhiều ký tự + dấu đô-la + hằng chuỗi + dấu đô-la + cùng tag vừa định nghĩa + dấu đô-la. Ví dụ cụ thể :

$$Toan's string constant$$
$BODY$Toan's string constant$BODY$

3. Ví dụ đơn giản

Ví dụ  1
Trong ví dụ sau, ta định nghĩa hàm phát sinh ngẫu nhiên một chuỗi các số

CREATE OR REPLACE FUNCTION random_numero(amount int)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
    ARRAY(
        SELECT chr((48 + round(random() * 9)) :: integer) 
        FROM generate_series(1,$1)
    ), '');
$BODY$
LANGUAGE sql VOLATILE;

Giải thích :
- Hàm có tên là random_numero, nhận một tham số kiểu int
- Hàm trả về kết quả là một chuỗi kiểu TEXT
- Trong ngoặc đô-la $BODY$ là thân hàm
- Thân hàm là một câu truy vấn SQL select trả về một chuỗi ngẫu nhiên gồm n số, với n là tham số amount hàm nhận vào. Chú ý, hàm không nhận biết tên tham số chuyển vào : Các tham số được sử dụng trong hàm dưới dạng $n; $1 trỏ đến tham số đầu tiên, $2 đến tham số thứ hai, vân vân.
- Với tham số truyền vào chỉ có kiểu (type) là bắt buộc, do đó hàm trên có thể được định nghĩa random_numero(int). Nhưng ta nên định nghĩa hàm với tên tham số cho dễ hiểu
- Trong trường hợp nếu tham số chuyển vào có dạng phức thì ta dùng đến toán tử dấu chấm để truy xuất đến thuộc tính của biến truyền vào. VD : $1.name
- Trong câu truy vấn này ta sử dụng hàm định nghĩa sẵn array_to_string để chuyển một mảng thành một chuỗi. Sử dụng hàm generate_series ở đây chủ yếu để phát sinh vòng lặp từ 1 đến n với n là giá trị của biến $1 truyền vào. Với mỗi vòng lặp này, ta phát sinh một số ngẫu nhiên trong khoảng từ 0 đến 9 để tạo thành một phần tử của mảng. :: integer dùng để ép kiểu biểu thức chr((48 + round(random() * 9)) về số nguyên. round(random() * 9) trả về giá trị từ 0 đến 9
- Cuối cùng "LANGUAGE sql VOLATILE" định nghĩa hàm này được viết bằng ngôn ngữ truy vấn SQL, volatile là giá trị mặc định của một phân loại (volatility classification) dùng để tối ưu hàm (hay không).

Gọi hàm này như sau : SELECT random_numero(5);

openspace_vn_db=# SELECT random_numero(5);
 random_numero 
---------------
 08182
(1 row)

Ví dụ 2
Định nghĩa hàm cộng hai số với tham số trả về (ouput parameter)

CREATE OR REPLACE FUNCTION add_two (IN a int, IN b int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;

SELECT add_two(4,58);

 add_two 
---------
      62
(1 row) 

Giải thích :
- Hàm này nhận vào hai tham số và trả về tổng của hai giá trị này
- Tiện ích của cách định nghĩa này là ta có thể trả về nhiều giá trị, chẳng hạn

CREATE FUNCTION sum_n_product (a int, b int, OUT sum int, OUT product int)
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

- Chú ý khi gọi hàm ta chỉ truyền vào các tham số in (tức chỉ 2 tham số a và b)
- Các tham số có thể được đánh dấu là IN, OUT, INOUT hay VARIADIC.
- Dấu chấm phẩy ở câu lệnh cuối của một hàm là tuỳ ý tức có cũng được hay không có cũng được. Do vậy, ở đây tuy không có dấu chấm phẩy ở cuối nhưng hàm vẫn hợp lệ.

Ví dụ3
Nếu hàm của bạn chỉ thực hiện các việc như UPDATE hoặc DELETE và không trả về giá trị gì, bạn có thể định nghĩa hàm trả về VOID.
Trong ví dụ sau, hàm thực hiện việc giấu thông tin về tên và tên nước của provider với việc dùng hàm md5 như sau :

CREATE OR REPLACE FUNCTION mask_provider()
RETURNS VOID AS
$BODY$
    UPDATE provider SET name = md5(name), country = md5(country);
$BODY$
LANGUAGE sql VOLATILE;

SELECT mask_provider();

Giải thích :
- Hàm thực hiện việc cập nhật và giấu thông tin tên và tên nước của provider bằng cách mã hoá hai thông tin này bằng hàm md5.

4. Ví dụ trả về nhiều dòng kết quả

Ví dụ 1
Hàm trả về danh sách tên của điện thoại di động trong bảng mobile.

CREATE OR REPLACE FUNCTION get_mobile_name()
RETURNS SETOF TEXT AS
$BODY$
    SELECT name FROM mobile;
$BODY$
LANGUAGE sql VOLATILE;

SELECT get_mobile_name();

get_mobile_name  
-------------------
 Samsung Galaxy S4
 iPhone 6
(2 rows)

Giải thích :
- Ở đây ta dùng từ khoá SETOF sau RETURNS để hàm trả về toàn bộ kết quả của câu truy vấn cuối trong hàm (trong ví dụ, hàm chỉ có một câu truy vấn).


Ví dụ 2
Hàm trả về danh sách tên của điện thoại di động trong bảng mobile, với kiểu trả về cũng chính là kiểu của cột name trong bảng mobile.

CREATE OR REPLACE FUNCTION get_mobile_name()
RETURNS SETOF mobile.name%TYPE AS
$BODY$
    SELECT name FROM mobile;
$BODY$
LANGUAGE sql VOLATILE;

Giải thích :
- %TYPE thực hiện việc chép kiểu : mobile.name%TYPE cung cấp kiểu của cột name trong bảng mobile.


Ví dụ 3
Hàm trả về nội dung của toàn bộ bảng mobile

CREATE OR REPLACE FUNCTION get_mobile()
RETURNS SETOF mobile AS
$BODY$
    SELECT * FROM mobile;
$BODY$
LANGUAGE sql VOLATILE;

SELECT * FROM get_mobile();

Giải thích :
- Ta có thể dùng bảng có sẵn trong CSDL làm kiểu trả về (ở đây là bảng mobile)


Ví dụ 4
Ta có thể định nghĩa lại kiểu của bảng trả về như sau

CREATE OR REPLACE FUNCTION get_mobile()
RETURNS TABLE(id integer, name text) AS
$BODY$
    SELECT id, name FROM mobile;
$BODY$
LANGUAGE sql VOLATILE;

SELECT * FROM get_mobile();

Giải thích :
- TABLE(id integer, name text) định nghĩa kiểu của bảng trả về gồm hai cột có kiểu lần lượt là integer và text. RETURNS TABLE(colums) thuộc về chuần gần đây của SQL nên nó có tính khả chuyển (portable) hơn là dùng SETOF.

5.Tóm tắt
Ta có thể tóm gọn việc định nghĩa hàm bằng ngôn ngữ truy vấn như sau

CREATE FUNCTION function_name ([ [ [IN | OUT | INOUT | VARIADIC] 

[argument_name] argument_type] [,  ...] ]) 

[RETURNS {[SETOF] return_type | TABLE(columns)} ] AS $$
     function_body_query;
$$ language sql;

Xem cụ thể tại đây CREATE FUNCTION