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

Введение

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

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

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

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

sudo -u postgres psql

Теперь вы должны увидеть приглашение PostgreSQL, которое выглядит как postgres=#.

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

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

Разберем эту команду:

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

После выполнения команды PostgreSQL подтвердит создание функции:

CREATE FUNCTION

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

SELECT get_total_products();

Вывод покажет значение, возвращаемое функцией:

 get_total_products
--------------------
                100
(1 row)

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

Создание функции с параметрами

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

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

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

Синтаксис аналогичен предыдущему шагу, с ключевым отличием в сигнатуре функции:

• add_numbers(a INTEGER, b INTEGER): Это определяет два параметра, a и b, оба типа INTEGER, которые должны быть предоставлены при вызове функции.

После выполнения команды вы увидите подтверждение CREATE FUNCTION.

Теперь протестируйте функцию, передав два числа в качестве аргументов:

SELECT add_numbers(15, 25);

Функция сложит два числа и вернет результат:

 add_numbers
-------------
          40
(1 row)

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

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

Здесь CREATE OR REPLACE обновит функцию, если она уже существует. Параметру greeting присвоено значение по умолчанию 'Hello'. Оператор || используется для конкатенации строк.

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

SELECT greet_user('Alex');

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

       greet_user
------------------------
 Hello, Alex!
(1 row)

Теперь вызовите ее снова, но на этот раз передайте пользовательское приветствие:

SELECT greet_user('Alex', 'Welcome');

Вывод теперь показывает ваше пользовательское сообщение:

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

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

Распространенное применение хранимых функций — выполнение вычислений над данными из таблиц. На этом шаге вы создадите таблицу, заполните ее данными, а затем используете функцию в запросе к этой таблице.

Сначала создайте простую таблицу с именем products для хранения названий и цен продуктов.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

Вы увидите сообщение подтверждения CREATE TABLE.

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

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

Вы увидите INSERT 0 3, что указывает на вставку трех строк.

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

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

После создания функции вы можете использовать ее непосредственно в запросе SELECT к таблице products. Этот запрос покажет исходную цену и цену с учетом налога для каждого продукта.

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

Вывод отобразит результаты вызова функции для каждой строки:

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

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

Удаление хранимой функции

Хорошей практикой является удаление объектов базы данных, которые больше не нужны. На этом шаге вы узнаете, как удалить или "сбросить" хранимую функцию из вашей базы данных. Мы удалим функцию get_total_products, созданную на первом шаге.

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

\df

Вы увидите список функций, включая get_total_products.

Чтобы удалить функцию, используйте команду DROP FUNCTION. Вы должны указать имя функции. Если функция имеет параметры, вам потребуется указать их типы, но поскольку get_total_products их не имеет, вы можете просто использовать имя.

DROP FUNCTION get_total_products();

PostgreSQL подтвердит действие:

DROP FUNCTION

Теперь, если вы снова выведете список функций с помощью \df, вы увидите, что get_total_products больше нет в списке.

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

DROP FUNCTION add_numbers(INTEGER, INTEGER);

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

DROP TABLE products;

На этом лабораторная работа завершена. Чтобы выйти из оболочки psql, введите \q и нажмите Enter.

Резюме

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