Với PL/pgSQL ta có thể dùng tất cả các kiểu dữ liệu, các toán tử và các hàm của SQL.
Ngoài ra, PL/pgSQL còn cho phép ta định nghĩa các hàm động (hàm liên quan đến các cột khác nhau, các bảng khác nhau mỗi khi ta thực thi nó), thực thi các lệnh điều kiện (IF, ELSE, CASE), các vòng lặp (LOOP, WHILE, FOR, FOREACH), xử lý các ngoại lệ và lỗi (EXCEPTION, RAISE), định nghĩa TRIGGER, con trỏ (CURSOR, FETCH, MOVE) ...
Kể từ PostgreSQL 9.0, PL/pgSQL được cài mặc định.
Định nghĩa một hàm đơn giản viết bằng PL/pgSQL có dạng sau :
CREATE FUNCTION function_name (argument_name argument_type, argument_name argument_type, ...)
RETURNS return_type AS $$
[ DECLARE
declarations ]
BEGIN
statements
END;
$$ language plpgsql;
Định nghĩa thân hàm phải là một lốc DECLARE-BEGIN-END. Trong đó, mỗi khai báo (declaration) và mỗi dòng lệnh (statement) kết thúc bằng dấu chấm phẩy.
Trong trường hợp phức tạp, lốc gốc này có thể có nhiều lốc con DECLARE-BEGIN-END, khi đó mỗi lốc con này kết thúc bằng END và dấu chấm phẩy theo sau.
Chú ý không nên nhầm lẫn : BEGIN/END của PL/pgSQL nhằm nhóm các lệnh vào một lốc. Còn BEGIN/END của SQL nhằm khởi đầu và kết thúc một giao dịch (transaction).
2. Ví dụ
Ví dụ 1
Ví dụ sau định nghĩa một hàm động cho phép lấy một giá trị ngẫu nhiên trong một cột của một bảng
CREATE OR REPLACE FUNCTION get_field(field_name varchar, table_name varchar, numrow int)
RETURNS SETOF varchar AS
$BODY$
BEGIN
RETURN QUERY EXECUTE 'SELECT ' || $1 || ' from ' || $2 ||' ORDER BY random()*' || $3 || ' LIMIT 1';
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT get_field('name','mobile',10);
Giải thích :
- Ở đây ta không cần dùng biến nên không có đoạn DECLARE.
- Câu truy vấn SQL được phát sinh động bằng cách nối chuỗi dùng các tham số là tên cột, tên bảng và một số. "ORDER BY random()*số LIMIT 1" cho phép lấy dòng đầu tiên của kết quả trả về với thứ tự ngẫu nhiên.
- Lệnh EXECUTE cho phép thực thi câu truy vấn SQL động định nghĩa bằng một chuỗi.
- RETURN QUERY trả về kết quả của một câu truy vấn (chứ không chỉ là một giá trị đơn giản) đó là lý do tại sao trong khai báo hàm ta dùng RETURNS SETOF.
- Ở đây ta dùng dấu nháy ' để định nghĩa các chuỗi, trong trường hợp chuỗi bao gồm nhiều dấu nháy ta có thể dùng bởi dấu ngoặc đô-la để bọc chuỗi để câu truy vấn dễ hiểu hơn.
- Câu gọi lệnh ở cuối trả về giá trị ngẫu nhiên của cột name trong bảng mobile.
Ví dụ2
Viết lại hàm trên để trả về một giá trị đơn giản
DROP FUNCTION IF EXISTS get_field(varchar, varchar, int);
CREATE OR REPLACE FUNCTION get_field(field_name varchar, table_name varchar, numrow int)
RETURNS varchar AS
$BODY$
DECLARE
field varchar;
BEGIN
EXECUTE 'SELECT ' || $1 || ' from ' || $2 ||' ORDER BY random()*' || $3 || ' LIMIT 1'
INTO field;
RETURN field;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT get_field('name','mobile',10);
Giải thích :
- Hàm này thực hiện cùng một chức năng như hàm trong ví dụ 1.
- Ta định nghĩa một biến field (trong đoạn DECLARE), thực thi câu truy vấn động và gán giá trị trả về vào biến này (EXCECUTE ... INTO ...) . Cuối cùng trả về kết quả này cho hàm.
- Hàm này trả về một giá trị đơn giản nên trong định nghĩa hàm ta không dùng SETOF.
Ví dụ3
Ta có bảng customer như sau :
Table "public.customer"
Column | Type | Modifiers
------------+-----------------------+-----------
id | integer | not null
address | character varying(64) |
first_name | character varying(32) |
last_name | character varying(32) |
email | character varying(64) |
Indexes:
"customer_pkey" PRIMARY KEY, btree (id)
Ta muốn tạo một bảng temp_customer có cấu trúc giống hệt bảng customer; và bảng này có dữ liệu là dữ liệu lấy ngẫu nhiên từ bảng customer
DROP TABLE IF EXISTS temp_customer;
CREATE TABLE temp_customer
(
id integer NOT NULL,
address character varying(64),
first_name character varying(32),
last_name character varying(32),
email character varying(64),
CONSTRAINT temp_customer_pkey PRIMARY KEY (id)
);
Để điền tự động thông tin vào bảng temp_customer ta định nghĩa hàm sau :
CREATE OR REPLACE FUNCTION fill_temp_customer()
RETURNS VOID AS
$BODY$
DECLARE
num_row int;
BEGIN
SELECT COUNT(*) INTO num_row FROM customer;
FOR i IN 1..1000
LOOP
INSERT INTO temp_customer(id, address, first_name, last_name, email)
values(i, get_field('address', 'customer', num_row),
get_field('first_name', 'customer', num_row), get_field('last_name', 'customer', num_row),
concat('customer_', i, '@openspace.vn'));
EXIT WHEN i > num_row;
END LOOP;
SELECT COUNT(*) INTO num_row FROM temp_customer;
IF num_row = 0 THEN
INSERT INTO temp_customer(id, address, first_name, last_name, email)
values(1, 'address 1', 'first_name 1', 'last_name 1', 'customer_1@openspace.vn');
INSERT INTO temp_customer(id, address, first_name, last_name, email)
values(2, 'address 2', 'first_name 2', 'last_name 2', 'customer_2@openspace.vn');
END IF;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT fill_temp_customer();
Giải thích :
- Hàm định nghĩa biến num_row.
- Hàm này nhằm điền dữ liệu vào bảng temp_customer và không trả về giá trị nên khai báo hàm dùng RETURNS VOID.
- Câu lệnh SELECT INTO đầu tiên nhằm đếm tổng số dòng của bảng customer. Chú ý lệnh SELECT INTO của PL/PgSQL này (nhằm gán kết quả trả về vào một biến) hoàn toàn khác lệnh SELECT INTO của PostgreSQL (nhằm tạo ra một bảng mới từ kết quả của một câu truy vấn).
- Vòng lặp FOR từ 1 đến 1000 nhằm chèn thông tin vào bảng temp_customer, với giá trị của address, first_name, last_name là giá trị tương ứng lấy ngẫu nhiên từ bảng customer bằng cách dùng hàm get_field định nghĩa trong ví dụ 1.
- Lệnh EXIT nhằm kết thúc vòng lặp trong trường hợp tổng số dòng trong bảng customer ít hơn 1000.
- Câu lệnh SELECT INTO tiếp theo nhằm đếm tổng số dòng của bảng temp_customer.
- Trong trường hợp bảng customer không có dữ liệu, dẫn đến bảng temp_customer cũng không có dữ liệu, lệnh IF tiếp theo cho phép chèn hai dòng dữ liệu vào bảng temp_customer.
