Возврат данных из хранимых процедур¶
С помощью хранимых процедур удобно обрабатывать данные, хранящиеся в базе. Но кроме обработки данных, хранимые процедуры должны ещё и возвращать результаты своей работы. Ниже рассматриваются основные способы возврата данных из хранимых процедур.
Возврат простых типов данных¶
Для возврата простых типов в заголовке хранимой процедуры достаточно указать тип данных, который будет возвращаться, а в теле самой процедуры использовать оператор RETURN
Пример функции, возвращающей значение типа int
CREATE OR REPLACE FUNCTION return_int() RETURNS int AS
$$
BEGIN
RETURN 1;
END
$$ LANGUAGE plpgsql;
db=# SELECT * FROM return_int();
return_int
------------
1
(1 row)
Возврат набора данных¶
Функции могут возвращать не только одно значение/строку, но и набор значений/строк. Для этого в заголовке функции перед типом возвращаемых данных надо вставить ключевое слово SETOF. В теле же функции надо использовать выражение RETURN NEXT для возврата каждой строки. Это выражение можно вызывать несколько раз и результатом каждого вызова будет новая строка в выходном наборе данных. Для выхода из процедуры надо использовать ключевое слово RETURN без параметров. Количество строк, возвращаемых хранимыми процедурами, может быть любым (от нуля до бесконечности).
Пример функции, возвращающей набор int
CREATE OR REPLACE FUNCTION return_setof_int() RETURNS SETOF int AS
$$
BEGIN
RETURN NEXT 1;
RETURN NEXT 2;
RETURN NEXT 3;
RETURN; -- Необязательный
END
$$ LANGUAGE plpgsql;
db=# SELECT * FROM return_setof_int();
return_setof_int
------------------
1
2
3
(3 rows)
Использование OUT-параметров¶
Определять структуру возвращаемых данных можно с помощью OUT-параметров (и INOUT).
Пример функции, использующей OUT-параметры для возврата данных.
CREATE OR REPLACE FUNCTION return_out_int(OUT result1 int, OUT result2 int) AS
$$
BEGIN
result1 := 1;
RETURN;
END
$$ LANGUAGE plpgsql;
db=# SELECT * FROM return_out_int();
result1 | result2
---------+---------
1 |
(1 row)
Использование выражения TABLE¶
Выражение TABLE очень похоже на OUT-параметры, которые просто вынесены в отдельно стоящую конструкцию языка. Основным отличием этого выражения от OUT-параметров является возможность возврата набора данных без использования ключевого слова SETOF в заголовке функции.
Пример функции, использующей конструкцию TABLE
CREATE OR REPLACE FUNCTION return_table() RETURNS table(id int, name varchar) AS
$$
BEGIN
id := 1;
name := 'name';
RETURN NEXT;
RETURN NEXT;
END
$$ LANGUAGE plpgsql;
db=# SELECT * FROM return_table();
id | name
----+------
1 | name
1 | name
(2 rows)
Возврат сложных типов данных¶
Возвращать из функций можно не только простые данные и их наборы, но и наборы данных с типами, определенными разработчиками.
Есть пользовательский данных, который хранит информацию о клиентах.
CREATE TYPE t_customer AS (
id int,
name varchar
);
Есть таблица, которая хранит информацию о клиентах
CREATE TABLE customers(
id serial PRIMARY KEY,
name varchar NOT NULL UNIQUE
);
db=# INSERT INTO customers(name) VALUES('name1');
INSERT 0 1
db=# INSERT INTO customers(name) VALUES('name2');
INSERT 0 1
Возврат пользовательских типов данных¶
Пример функции, возвращающей набор данных сложного пользовательского типа.
CREATE OR REPLACE FUNCTION return_t_customers() RETURNS SETOF t_customer AS
$$
DECLARE
_result t_customer;
BEGIN
FOR _result.id, _result.name IN SELECT id, name FROM customers
LOOP
RETURN NEXT _result;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
db=# SELECT * FROM return_t_customers();
id | name
----+-------
1 | name1
2 | name2
(2 rows)
Предупреждение
Часто разработчики облегчают себе жизнь и не используют присваивание полей сложных типов в явном виде, а отдают это на откуп скриптовому движку, который присваивает поля сложных структур данных не по имени, а по номеру их позиции относительно начала определения типа.
Пример
FOR _result IN SELECT * FROM customers --
LOOP
RETURN NEXT _result;
END LOOP;
RETURN;
Выше приведенное присваивание эквивалентно следующему псевдокоду
FOR _result[1], _result[2] IN SELECT customers[1], customers[2] FROM customers --
Это в корне неверно, так как порядок полей в таблице, если он не задан явным образом, считается в теории баз данных неопределенным. Поэтому выше приведенная конструкция может не всегда корректно работать. Хорошо, если она выдаст исключение при выполнении, хуже будет, если она выполнится без ошибок, но значения полей в результате будут перепутаны. Последствия могут быть катастрофическими, если это какая-то финансовая или медицинская система.
Возврат табличных типов данных¶
Вместо пользовательских данных бывает удобно использовать табличные типы данных, которые определяются структурой таблиц. Никаких отличий табличных типов данных от пользовательских нет, разница только в источнике их происхождения.
CREATE OR REPLACE FUNCTION return_customers() RETURNS SETOF customers AS
$$
SELECT * FROM customers
$$ LANGUAGE sql;
db=# SELECT * FROM return_customers();
id | name
----+-------
1 | name1
2 | name2
(2 rows)
Использование курсоров¶
Возврат данных с помощью курсоров является самым гибким способом возврата, но и самым сложным как в реализации, так и в использовании. Непосредственный возврат данных происходит не как результат выполнения функции. Для получения данных нужны дополнительные усилия.
CREATE OR REPLACE FUNCTION return_cursor() RETURNS refcursor AS
$$
DECLARE
_result CONSTANT refcursor := '_result';
BEGIN
OPEN _result FOR SELECT * FROM customers;
RETURN _result;
END
$$ LANGUAGE plpgsql;
Для получения данных необходимо явным образом создавать транзакцию, так как курсоры существуют только в рамках одной транзакции. Запрос FETCH ALL FROM <cursor_name> возвращает все данные курсора;
db=# BEGIN;
BEGIN
db=# SELECT * FROM return_cursor();
return_cursor
---------------
_result
(1 row)
db=# FETCH ALL FROM _result;
id | name
----+-------
1 | name1
2 | name2
(2 rows)
db=# COMMIT;
COMMIT
У этого способа есть два приятных бонуса:
- возвращать можно больше одного курсора
- этот способ можно комбинировать с другими вариантами возврата данных