SQL-Ex blog

Что такое функция пользователя?

Функция пользователя представляет собой группу произвольных операторов SQL, предназначенных для выполнения некоторой задачи. Эти функции не поставляются из коробки и обычно создаются для обработки специфичных сценариев. Можно выполнять операции select, insert, update, delete внутри функции. Функция в PostgreSQL может создаваться на любом языке, таком как SQL, C, PL/pgSQL, Python и т.д.

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

Как создать функцию пользователя?

Функцию можно создать двумя способами - либо с помощью кода PostgreSQL, либо в PgAdmin GUI. Давайте рассмотрим оба варианта.

Базовый синтаксис

В этом синтаксисе после предложения CREATE OR REPLACE FUNCTION указывается имя функции (function_name) со списком аргументов или параметров. Затем после ключевого слова RETURNS объявляется тип данных возвращаемого значения (return_datatype ). return_datatype может быть одним из типов данных PostgreSQL, например, character, integer, double и т.п. Так же из функции PostgreSQL можно вернуть таблицу.

Далее после ключевого слова DECLARE объявляются используемые в функции переменные IN, OUT. Далее в блоке BEGIN-END задается тело функции (function_body). function_body обычно содержит бизнес-логику функции. Затем после ключевого слова RETURN указывается имя переменной (variable_name), которая содержит возвращаемое из функции значение.

Наконец, после ключевого слова LANGUAGE указывается язык (language_name), на котором написана функция.

CREATE [OR REPLACE] FUNCTION function_name (arguments) 

RETURNS return_datatype AS $variable_name$

   DECLARE

      declaration;

      [...]

   BEGIN

      < function_body >

      [.. logic]

      RETURN { variable_name | value }

   END; 

LANGUAGE language_name;

Запросы можно выполнять либо в оболочке PostgreSQL (PSQL), либо в среде PgAdmin (query tool).

Пример

Тут мы создаем функцию, которая возвращает текущую дату и время сервера.

CREATE FUNCTION getTimestamp() RETURNS timestamp AS $$

BEGIN

RETURN CURRENT_TIMESTAMP;

END; $$

LANGUAGE PLPGSQL;

Для получения списка всех функций в пределах схемы базы данных:

df <схема>.*



Давайте выполним нашу функцию:

PgAdmin GUI

Давайте создадим ту же функцию в программе PgAdmin.

Шаг 1 -> В навигаторе перейти к серверам->Database->Schema->Function->Create->Function, как показано ниже:



Шаг 2 -> Указать имя, владельца, схему и написать комментарий, если нужно.



Шаг 3 -> Задать аргументы функции, тип возвращаемого значения и язык, что объяснялось в предыдущем разделе.



Шаг 4 -> Ввести бизнес-логику на вкладке code



Шаг 5 -> Затем ввести параметры на вкладке Parameters и нажать кнопку Save для создания функции.

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

Давайте теперь рассмотрим примеры нескольких функций, чтобы лучше понять концепцию.

Замечание. Для выполнения запросов можно использовать либо инструмент запросов в PgAdmins, либо консоль Psql. В этом руководстве мы использовали инструмент запросов PgAdmin для этой цели. Оба этих редактора входят в состав последних версий PgAdmin.

Пример 1: сложить два числа

Здесь создается функция с именем addNumbers. Эта функция принимает два целочисленных параметра на входе и возвращает одно целое число на выходе.

CREATE FUNCTION addNumbers(val1 integer, val2 integer) RETURNS integer AS $$

BEGIN

RETURN val1 + val2;

END; $$

LANGUAGE PLPGSQL;

Вот эта функция, созданная в PgAdmin:



Функция появляется слева в ветке Functions:



Теперь давайте выполним эту функцию, как показано ниже:

Пример 2: В этом примере мы увидим, как выполнить условный оператор SELECT в функции

Эта функция используется для выполнения операции выборки из таблицы Stocks. Функция возвращает количество тех акций, чья стоимость меньше, чем входной параметр price_cap. Переменная, объявленная в блоке declare содержит выходное значение.

create function get_stocks(price_cap int)

returns int

language plpgsql

as

$$

declare

   stock_count integer;

begin

   select count(*) 

   into stock_count

   from public."Stocks"

   where stock_price < price_cap;

   

   return stock_count;

end;

$$;

Давайте создадим ее в PgAdmin.



Давайте взглянем на данные в таблице:



Теперь выполним функцию, чтобы проверить ее поведение:



Что касается нашей таблицы, то имеется 3 акции, чья стоимость меньше 1000, и функция их и возвращает.

Пример 3: возвращение таблицы

Функция может применяться также для возвращения таблицы, если это требуется. Мы будем использовать следующую таблицу для демонстрации данной функциональности.



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

CREATE OR REPLACE FUNCTION get_allStocks (price_cap int) 

    RETURNS TABLE (

        stock_serial_no int,

        stock varchar,

        price_of_stock bigint

) 

AS $$

BEGIN

    RETURN QUERY SELECT

        stock_id,

        stock_name,

        stock_price

    FROM

        public."Stocks"

    WHERE

        stock_price < price_cap;

END; $$ 

LANGUAGE 'plpgsql';

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

Преимущества использования функций пользователя

Имеется много причин использовать функции:

1. Легко вызывать


2. Может использоваться в разных местах без ограничений, которые есть у хранимых процедур


3. Допускается перегрузка функции, функции, имеющие различные параметры, могут иметь одинаковое имя