Разработка хранимых функций PostgreSQL

Введение

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

Skills Graph

Определение базовой хранимой функции

В этом шаге вы узнаете, как определить базовую хранимую функцию в PostgreSQL. Хранимые функции (Stored functions) - это многократно используемые блоки кода, которые могут быть выполнены внутри базы данных.

Сначала подключитесь к базе данных PostgreSQL от имени пользователя postgres:

sudo -u postgres psql

Теперь давайте создадим функцию с именем add_numbers, которая принимает два целых числа в качестве входных данных и возвращает их сумму. Выполните следующую SQL-команду:

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Давайте разберем этот код:

• CREATE FUNCTION add_numbers(a INTEGER, b INTEGER): Это определяет имя функции как add_numbers и указывает два целочисленных входных параметра, a и b.
• RETURNS INTEGER: Это указывает, что функция будет возвращать целочисленное значение.
• AS $$ ... $$: Это определяет тело функции. Символы $$ используются для разделения тела функции.
• BEGIN ... END;: Этот блок содержит логику функции.
• RETURN a + b;: Это вычисляет сумму a и b и возвращает результат.
• LANGUAGE plpgsql: Это указывает, что функция написана на процедурном языке PL/pgSQL.

После выполнения SQL-запроса вы должны увидеть следующий вывод:

CREATE FUNCTION

Это указывает на то, что функция была успешно создана.

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

\df

Вы должны увидеть add_numbers в списке.

Наконец, давайте протестируем функцию. Выполните следующий запрос:

SELECT add_numbers(5, 3);

Вывод должен быть:

 add_numbers
-------------
           8
(1 row)

Это подтверждает, что функция работает правильно.

Добавление входных параметров со значениями по умолчанию

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

Давайте создадим новую функцию под названием add_numbers_default, которая принимает два целочисленных входных параметра, a и b, со значениями по умолчанию 0. Выполните следующую SQL-команду:

CREATE OR REPLACE FUNCTION add_numbers_default(a INTEGER DEFAULT 0, b INTEGER DEFAULT 0)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Давайте разберем этот код:

• CREATE OR REPLACE FUNCTION add_numbers_default(a INTEGER DEFAULT 0, b INTEGER DEFAULT 0): Это определяет имя функции как add_numbers_default и указывает два целочисленных входных параметра, a и b, со значениями по умолчанию 0. CREATE OR REPLACE позволяет создать функцию, если она не существует, или заменить ее, если она существует.
• RETURNS INTEGER: Это указывает, что функция будет возвращать целочисленное значение.
• AS $$ ... $$: Это определяет тело функции.
• BEGIN ... END;: Этот блок содержит логику функции.
• RETURN a + b;: Это вычисляет сумму a и b и возвращает результат.
• LANGUAGE plpgsql: Это указывает, что функция написана на процедурном языке PL/pgSQL.

После выполнения SQL-запроса вы должны увидеть следующий вывод:

CREATE OR REPLACE FUNCTION

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

1. Предоставьте оба параметра:

SELECT add_numbers_default(5, 3);

Вывод должен быть:

 add_numbers_default
---------------------
                     8
(1 row)

2. Предоставьте только первый параметр:

SELECT add_numbers_default(5);

Вывод должен быть:

 add_numbers_default
---------------------
                     5
(1 row)

В этом случае a равно 5, а b по умолчанию равно 0, поэтому результат равен 5.

3. Не предоставляйте никаких параметров:

SELECT add_numbers_default();

Вывод должен быть:

 add_numbers_default
---------------------
                     0
(1 row)

В этом случае и a, и b по умолчанию равны 0, поэтому результат равен 0.

Выполнение функции в запросе

В этом шаге вы узнаете, как выполнить функцию в запросе. Это демонстрирует, как хранимые функции (stored functions) могут использоваться для манипулирования и извлечения данных.

Сначала давайте создадим простую таблицу под названием numbers с двумя целочисленными столбцами, num1 и num2:

CREATE TABLE numbers (
  num1 INTEGER,
  num2 INTEGER
);

После выполнения SQL-запроса вы должны увидеть следующий вывод:

CREATE TABLE

Теперь давайте вставим некоторые примеры данных в таблицу numbers:

INSERT INTO numbers (num1, num2) VALUES (10, 5);
INSERT INTO numbers (num1, num2) VALUES (20, 3);
INSERT INTO numbers (num1, num2) VALUES (30, 0);

После выполнения SQL-запросов вы должны увидеть следующий вывод для каждого оператора INSERT:

INSERT 0 1

Теперь давайте выполним функцию add_numbers_default в запросе, чтобы вычислить сумму num1 и num2 для каждой строки в таблице numbers:

SELECT num1, num2, add_numbers_default(num1, num2) AS sum FROM numbers;

Вывод должен быть:

 num1 | num2 | sum
------+------+-----
   10 |    5 |  15
   20 |    3 |  23
   30 |    0 |  30
(3 rows)

Как видите, функция add_numbers_default была выполнена для каждой строки в таблице numbers, и результат был отображен в столбце sum.

Давайте попробуем другой запрос, в котором мы предоставляем только один параметр функции add_numbers_default. Мы будем использовать num1 в качестве первого параметра, а num2 оставим по умолчанию равным 0:

SELECT num1, add_numbers_default(num1) AS sum FROM numbers;

Вывод должен быть:

 num1 | sum
------+-----
   10 |  10
   20 |  20
   30 |  30
(3 rows)

Наконец, давайте попробуем запрос, в котором мы не предоставляем никаких параметров функции add_numbers_default:

SELECT add_numbers_default() AS sum FROM numbers;

Вывод должен быть:

 sum
-----
   0
   0
   0
(3 rows)

Удаление неиспользуемой функции

В этом шаге вы узнаете, как удалить неиспользуемую функцию из вашей базы данных PostgreSQL. Удаление неиспользуемых функций помогает поддерживать вашу базу данных в чистоте и порядке. Мы удалим функцию add_numbers, которую мы создали на первом шаге.

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

\df

Вы должны увидеть add_numbers в списке. Если вы ее не видите, это означает, что вы либо не создали ее на первом шаге, либо уже удалили.

Теперь давайте удалим функцию add_numbers, используя команду DROP FUNCTION:

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Давайте разберем этот код:

• DROP FUNCTION add_numbers(INTEGER, INTEGER): Это указывает, что мы хотим удалить функцию с именем add_numbers. Важно указать типы входных параметров (в данном случае, INTEGER, INTEGER), потому что PostgreSQL допускает перегрузку функций (function overloading) (наличие нескольких функций с одним и тем же именем, но разными типами параметров).

После выполнения SQL-запроса вы должны увидеть следующий вывод:

DROP FUNCTION

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

\df

Вы больше не должны видеть add_numbers в списке функций.

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

DROP TABLE numbers;

Итог

В этой лабораторной работе вы узнали, как разрабатывать хранимые функции (stored functions) PostgreSQL. Вы рассмотрели определение базовой хранимой функции, добавление входных параметров со значениями по умолчанию, выполнение функции в запросе и удаление неиспользуемой функции. Эти навыки являются основополагающими для создания более сложных и эффективных приложений баз данных.