Пользовательские функции и процедуры

Евгения Кузина

Обзор

Пользовательские функции и процедуры PostgreSQL предназначены для выполнения специализированных задач. Обычно они используются для обработки определенных пользовательских сценариев.

Пользовательские функции

В ADPG/PostgreSQL существуют следующие типы функций:

  • Функции на языке запросов — функции, написанные на SQL. Подробнее этот тип функции описан ниже.

  • Функции на процедурных языках — функции, написанные на других языках, помимо SQL и C. Такие языки обычно называются процедурными языками (Procedural Languages, PL), например, PL/pgSQL, PL/Tcl. Процедурные языки не встроены в серверную среду выполнения ADPG/PostgreSQL и подгружаются в виде модулей.

  • Внутренние функции — функции, написанные на языке C, статически связанные с сервером ADPG/PostgreSQL. Все внутренние функции объявляются во время инициализации кластера базы данных. Пользователи могут использовать CREATE FUNCTION для создания дополнительного псевдонима для внутренней функции. См. Internal functions.

  • Функции языка C — функции, написанные на C. Эти функции компилируются в динамически загружаемые объекты (также называемые разделяемыми библиотеками) и загружаются сервером по требованию. Динамическая загрузка отличает функции языка C от внутренних функций, так как фактические правила написания кода для них по сути одни и те же. См. C-language functions.

Каждый тип функции может принимать базовые и составные типы в качестве аргументов (параметров). Кроме того, функции могут возвращать базовый тип, составной тип или набор базовых и составных значений.

Вы можете использовать команду CREATE FUNCTION, чтобы определить новую функцию, ALTER FUNCTION, чтобы изменить определение функции и DROP FUNCTION, чтобы удалить функцию. DROP FUNCTION требует указания типов аргументов, поскольку может существовать несколько функций с одним и тем же именем и разными списками аргументов. См. Function overloading.

Пользовательские процедуры

Процедура — это объект базы данных, аналогичный функции и имеющий следующие ключевые отличия:

  • Процедуры создаются с помощью команды CREATE PROCEDURE.

  • Процедуры не возвращают значение, поэтому CREATE PROCEDURE не содержит выражения RETURNS. Однако процедуры могут возвращать данные через выходные параметры.

  • Функции вызываются как часть запроса или команды DML, а процедуры вызываются отдельно командой CALL.

  • Процедура может фиксировать или откатывать транзакции во время своего выполнения (затем автоматически начинать новую транзакцию), если вызывающая команда CALL не включена в явный блок транзакции.

  • Некоторые атрибуты функции (например, STRICT) нельзя применять к процедурам. Эти атрибуты влияют на вызовы функций в запросах и не имеют отношения к процедурам.

В разделах ниже описывается, как определять функции, что также применимо к процедурам, принимая во внимание приведенные выше соображения.

Функции и процедуры также известны как подпрограммы. ADPG/PostgreSQL предоставляет команды ALTER ROUTINE и DROP ROUTINE, которые могут работать с функциями и процедурами без указания точного типа объекта. Обратите внимание, что команды CREATE ROUTINE не существует.

Функции на языке запросов SQL

SQL-функции выполняют произвольный список операторов SQL и возвращают результат последнего запроса в списке. Используйте команду CREATE FUNCTION, чтобы создать функцию.

В простом случае будет возвращена первая строка результата последнего запроса. Помните, что первая строка многострочного результата точно определена только в том случае, если вы используете ORDER BY. Если последний запрос не возвращает ни одной строки, будет возвращено значение NULL.

Вы также можете объявить SQL-функцию, которая возвращает набор (несколько строк). Для этого укажите тип возвращаемого значения функции как SETOF <тип> или объявите функцию как RETURNS TABLE(<столбцы>). В этом случае будут возвращены все строки результата последнего запроса.

Тело SQL-функции должно представлять собой список операторов SQL, разделенных точкой с запятой. Точка с запятой после последнего оператора необязательна. Операторы SQL могут включать запросы SELECT, запросы на изменение данных (INSERT, UPDATE и DELETE) и другие команды SQL. В SQL-функциях нельзя использовать команды управления транзакциями (например, COMMIT, SAVEPOINT) и некоторые служебные команды (например, VACUUM). Последним оператором должен быть либо SELECT, либо команда INSERT, UPDATE или DELETE, содержащая выражение RETURNING.

Также можно создать SQL-функцию, которая выполняет действия, но не возвращает значение. Вы можете определить её как возвращающую void. Например, приведенная ниже функция удаляет строки, в которых значение quantity равно 0, из таблицы books:

CREATE FUNCTION delete_books() RETURNS void AS $$
    DELETE FROM books
        WHERE quantity = 0;
$$ LANGUAGE SQL;

SELECT delete_books();

Вы также можете выполнить этот код как процедуру:

CREATE PROCEDURE delete_books_proc() AS $$
    DELETE FROM books
        WHERE quantity = 0;
$$ LANGUAGE SQL;

CALL delete_books_proc();

Синтаксис команд CREATE FUNCTION и CREATE PROCEDURE требует, чтобы тело функции было записано как строковая константа. Обычно удобнее использовать долларовые кавычки для строковой константы (cм. Dollar-quoted string constants). Если вы решите использовать обычный синтаксис строковых констант, заключенных в одинарные кавычки, необходимо удваивать одинарные кавычки (') и использовать обратную косую черту (\) для экранирования символов в теле функции (cм. String constants).

ПРИМЕЧАНИЕ
Прежде чем начинать выполнение команд, PostgreSQL парсит тело SQL-функции. Если функция содержит команды, изменяющие системные каталоги (например, CREATE TABLE), действия этих команд не будут видны во время анализа последующих команд функции. Команды CREATE TABLE table1 (…​); INSERT INTO table1 VALUES(…​);, упакованные в одну функцию SQL, не будут работать должным образом, поскольку table1 не существует при анализе команды INSERT. В сценариях такого типа рекомендуется использовать функции PL/pgSQL вместо SQL-функций.

SQL-функции с базовыми типами

Простейшая SQL-функция не имеет аргументов и возвращает базовый тип:

CREATE FUNCTION my_function1() RETURNS integer AS $$
    SELECT 1;
$$ LANGUAGE SQL;

SELECT my_function();

Результат:

my_function
-------------
           1

Следующая SQL-функция принимает базовые типы в качестве аргументов:

CREATE FUNCTION my_calc(a integer, b integer) RETURNS integer AS $$
    SELECT a * b;
$$ LANGUAGE SQL;

SELECT my_calc(15, 13) AS answer;

Результат:

answer
--------
    195

На аргументы SQL-функции можно ссылаться в теле функции, используя имена или номера. Приведенную выше функцию можно переписать без имен аргументов:

CREATE FUNCTION my_calc1(integer, integer) RETURNS integer AS $$
    SELECT $1 * $2;
$$ LANGUAGE SQL;

Функция ниже выполняет вычисления и возвращает обновленный баланс как результат последней команды SELECT:

CREATE FUNCTION function1(account_id integer, income numeric) RETURNS numeric AS $$
    UPDATE accounts
        SET balance = balance + income
        WHERE account_id = function1.account_id;
    SELECT balance FROM accounts WHERE account_id = function1.account_id;
$$ LANGUAGE SQL;

В этом примере имя первого аргумента — account_id, и имя столбца таблицы такое же — account_id. Чтобы обратиться к аргументу в команде UPDATE, используйте имя аргумента с именем функции — function1.account_id. Также можно изначально указать другое имя аргумента.

Функцию function1 можно переписать, используя выражения RETURNING:

CREATE FUNCTION function1(account_id integer, income numeric) RETURNS numeric AS $$
    UPDATE accounts
        SET balance = balance + income
        WHERE account_id = function1.account_id
    RETURNING balance;
$$ LANGUAGE SQL;
ПРИМЕЧАНИЕ
Если последнее выражение SELECT или RETURNING в SQL-функции не возвращает объявленный тип, PostgreSQL, если это возможно, приведет значение к требуемому типу, с помощью неявного приведения или приведения по присваиванию. В противном случае необходимо явно привести результат. Для получения дополнительной информации см. Type Conversion и CREATE CAST.

Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию используются, когда функция вызывается с недостаточным количеством аргументов. Поскольку аргументы можно опускать только в конце списка аргументов, все параметры после параметра со значением по умолчанию также получат значения по умолчанию. Например:

CREATE FUNCTION function_sum (a int, b int DEFAULT 10, c int DEFAULT 20)
RETURNS int  AS $$
    SELECT a + b + c;
$$ LANGUAGE SQL;

SELECT function_sum(1, 2, 3);

Результат — 6.

Вы можете вызвать эту функцию с одним аргументом 5:

SELECT function_sum(5);

Результат — 35.

SQL-функции с составными типами

В функциях с аргументами составных типов необходимо указывать не только аргумент, но и какой атрибут (поле) этого аргумента следует использовать.

Например, создайте следующую таблицу:

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INT NOT NULL,
  price NUMERIC);

INSERT INTO books (title, author_id, price) VALUES
('Mrs. Dalloway',1,50),
('To the Lighthouse',1,61),
('To Kill a Mockingbird',2,48);

Определите функцию, которая добавляет налог к цене:

CREATE FUNCTION add_tax(books) RETURNS numeric AS $$
    SELECT $1.price * 1.2;
$$ LANGUAGE SQL;

SELECT title, add_tax(books.*) AS price_with_tax
    FROM book1;

Результат:

        title         | price_with_tax
-----------------------+----------------
 Mrs. Dalloway         |           60.0
 To the Lighthouse     |           73.2
 To Kill a Mockingbird |           57.6
(3 rows)

Необходимое поле таблицы аргументов указывается после точки (.) — $1.price. Чтобы выбрать всю строку таблицы как составное значение, используется звездочка (*) — books.*. См. Using composite types in queries.

SQL-функции с выходными параметрами

Альтернативный способ описания результатов функции — определение её с выходными параметрами. Выходные параметры предоставляют удобный способ создания функций, возвращающих несколько столбцов. Например:

CREATE FUNCTION function_calc (a int, b int, OUT sum int, OUT product int) AS $$
    SELECT a + b, a * b;
$$ LANGUAGE SQL;

 SELECT * FROM function_calc(5,6);

Результат:

sum | product
-----+---------
  11 |      30

Обратите внимание, что приведенный выше код создает анонимный составной тип для возвращаемого значения функции. Он имеет тот же результат, что и следующие команды:

CREATE TYPE my_type AS (sum int, product int);

CREATE FUNCTION function_calc (int, int) RETURNS my_type AS $$
SELECT $1 + $2, $1 * $2;
$$ LANGUAGE SQL;

Заметьте, что выходные параметры не включаются в список аргументов при вызове такой функции из SQL. PostgreSQL учитывает только входные параметры для определения сигнатуры вызова функции. Это также означает, что только входные параметры имеют значение при обращении к функции для таких целей, как её удаление. Можно удалить вышеуказанную функцию с помощью одной из следующих команд:

DROP FUNCTION function_calc (a int, b int, OUT sum int, OUT product int);
DROP FUNCTION function_calc (int, int);

Параметры SQL-функций можно объявить следующим образом:

  • INT — входной параметр, часть списка аргументов при вызове.

  • OUT — выходной параметр, часть типа результата.

  • INOUT — параметр является как входным параметром (часть списка аргументов при вызове), так и выходным параметром (часть типа результата).

  • VARIADIC — указывает, что функция может принимать переменное количество аргументов. См. SQL-функции с переменным числом аргументов.

SQL-процедуры с выходными параметрами

Выходные параметры также поддерживаются в процедурах, но они работают немного по-другому. Выходные параметры должны быть включены в список аргументов команды CALL. Например, function1 из примера выше можно переписать следующим образом:

CREATE PROCEDURE proc1(account_id integer, income numeric, OUT new_balance numeric) AS $$
    UPDATE accounts
        SET balance = balance + income
        WHERE account_id = proc1.account_id
    RETURNING balance;
$$ LANGUAGE SQL;

Чтобы вызвать эту процедуру, необходимо указать аргумент, соответствующий параметру OUT. Обычно используется NULL:

CALL proc1(2, 100.0, NULL);

SQL-функции с переменным числом аргументов

Функции SQL могут быть объявлены так, чтобы принимать переменное количество аргументов. Все необязательные аргументы должны иметь один и тот же тип данных. Необязательные аргументы передаются функции в виде массива. Для этого последний параметр объявляется как тип массива и помечается как VARIADIC. Например, следующая функция находит максимум из произвольного числа аргументов:

CREATE FUNCTION max_value(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT max(arr[i]) FROM generate_subscripts(arr, 1) g(i);
$$ LANGUAGE SQL;

SELECT max_value(16,7,5,4,67);

Результат:

max_value
----------
67

SQL-функции, возвращающие множества

Когда функция SQL возвращает SETOF <тип>, каждая строка её вывода возвращается как элемент результирующего набора. Такие функции часто используются в выражении FROM. Например:

CREATE TABLE employees (id int, report_to int, name text);
INSERT INTO employees VALUES (1, 0, 'Joe');
INSERT INTO employees VALUES (2, 1, 'Ed');
INSERT INTO employees VALUES (3, 1, 'Mary');
INSERT INTO employees VALUES (4, 2, 'Sara');
INSERT INTO employees VALUES (5, 4, 'Ivan');

CREATE FUNCTION get_employees(int) RETURNS SETOF employees AS $$
    SELECT * FROM employees WHERE report_to = $1;
$$ LANGUAGE SQL;

SELECT * FROM get_employees(1);

Результат:

 id | report_to | name
----+-----------+------
  2 |         1 | Ed
  3 |         1 | Mary

Часто бывает полезно сконструировать результат запроса путем многократного вызова функции, возвращающей множество, при этом параметры для каждого вызова берутся из последовательных строк таблицы или подзапроса. Для этого используется ключевое слово LATERAL. Например, имеется следующая таблица tree:

  name  | parent
--------+--------
 Parent |
 Child1 | Parent
 Child2 | Parent
 Child3 | Parent
 Child4 | Child1
 Child5 | Child1

Создайте функцию, которая выбирает дочерние ноды:

CREATE FUNCTION get_children(text) RETURNS SETOF text AS $$
    SELECT name FROM tree WHERE parent = $1
$$ LANGUAGE SQL STABLE;

Выполните следующий запрос:

SELECT name, child FROM tree, LATERAL get_children(name) AS child;

Результат:

  name  | child
--------+--------
 Parent | Child3
 Parent | Child2
 Parent | Child1
 Child1 | Child5
 Child1 | Child4
ПРИМЕЧАНИЕ
Если последней командой функции является INSERT, UPDATE или DELETE с RETURNING, команда будет выполнена до завершения, даже если функция не объявлена как возвращающая SETOF<тип>. Любые дополнительные строки, созданные с помощью выражения RETURNING, не будут возвращены, но все изменения таблицы будут произведены.

SQL-функции, возвращающие таблицы

Есть еще один способ объявить функцию как возвращающую множество. Можно использовать выражение RETURNS TABLE(<столбцы>). Это эквивалентно использованию одного или нескольких параметров OUT или объявлению функции как возвращающей SETOF <тип>. Например:

CREATE TABLE my_table (x int, y int);
INSERT INTO my_table VALUES (1, 11), (2, 12), (3, 13), (4, 14);

CREATE FUNCTION function_calc(a int) RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + my_table.x, $1 * my_table.y FROM my_table;
$$ LANGUAGE SQL;

SELECT * FROM function_calc(5);

Результат:

 sum | product
-----+---------
   6 |      55
   7 |      60
   8 |      65
   9 |      70

Функции на процедурных языках

Стандартный дистрибутив ADPG/PostgreSQL включает четыре процедурных языка: PL/pgSQL, PL/Tcl, PL/Perl и PL/Python.

Рассмотрим функцию, написанную на PL/pgSQL. Функция PL/pgSQL также создается с помощью команды CREATE FUNCTION:

CREATE FUNCTION my_function(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

PL/pgSQL — язык с блочной структурой. Полный текст тела функции должен быть блоком. Блок определяется следующим образом:

[ <<метка>> ]
[ DECLARE
    объявения ]
BEGIN
    операторы
END [ метка ];

Метка необходима только в том случае, если вы хотите идентифицировать блок для использования в операторе EXIT или уточнить имена переменных, объявленных в блоке. Все переменные, используемые в блоке, должны быть объявлены в разделе блока DECLARE. См. Declarations.

Следующая функция определяет переменную passed и сохраняет в ней результат проверки пароля:

CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
    SELECT  (pwd = $2) INTO passed
    FROM    pwds
    WHERE   username = $1;
       RETURN passed;
END;
$$  LANGUAGE plpgsql

Как и в SQL-функциях, на аргументы можно ссылаться по их номерам.

Для получения дополнительной информации о функциях PL/pgSQL обратитесь к статье PL/pgSQL — процедурный язык SQL.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней