Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
5 июня

Функции и хранимые процедуры в PostgreSQL: зачем нужны и как применять в реальных примерах

Функции и хранимые процедуры в PostgreSQL: зачем нужны и как применять в реальных примерах
Источник:
Просмотров:
2189

Как устроены функции и хранимые процедуры и как их применять для повторного использования запросов.

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

  • что такое функции и хранимые процедуры;
  • разница между функциями и хранимыми процедурами;
  • создание функций и хранимых процедур;
  • применение функций и хранимых процедур в реальных примерах.

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

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

Функции Хранимые процедуры
Функция имеет возвращаемый тип и возвращает значение Хранимая процедура не имеет возвращаемого типа, но имеет выходные аргументы
Использование DML (insert, update, delete) запросов внутри функции невозможно. В функциях разрешены только SELECT-запросы Использование DML-запросов (insert, update, delete) возможно в хранимой процедуре.
Функция не имеет выходных аргументов Хранимая процедура имеет и входные, и выходные аргументы
Вызов хранимой процедуры из функции невозможно Использование или же управление транзакциями возможно в хранимой процедуре
Вызов функции внутри SELECT запросов возможен Вызов хранимой процедуры из SELECT запросов невозможно

Давайте рассмотрим создание функции в PostgreSQL. Следующий блок кода иллюстрирует, как создавать функцию.

CREATE [or REPLACE] FUNCTION function_name(param_list)
   RETURNS return_type
   LANGUAGE plpgsql
  as
$$
DECLARE
-- variable declaration
BEGIN
 -- logic
END;
$$

Оператор:

  • create [or replace] function имя_функции — создает или заменяет функцию, если она существует, с заданным именем и параметрами;
  • returns return_type — тип данных, который возвращает функция;
  • язык plpgsql — указывает на процедурное расширение PostgreSQL;
  • внутри знака $ является телом функции;
  • declare — показывает, как объявляются или инициализируются переменные;
  • блок кода [begin — end] — содержит всю логику функции;
  • begin — указывает на начало запросов;
  • end — указывает конец функции.

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

CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)
    RETURNS numeric(10, 2)
    LANGUAGE plpgsql
AS
$$
DECLARE
    itemCost numeric(10, 2);
begin
    SELECT MAX(cost)
    INTO itemCost
    FROM purchases
    WHERE user_id = customer_id;
    RETURN itemCost;
end;
$$;

Важно отметить:

  • Declare itemCost integer — объявляем локальную переменную;
  • SELECT max(cost) INTO itemCost — как мы инициализируем переменную itemCost;
  • RETURN itemCost — возвращает значение функции.

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

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $
    stored_procedure_body;
$;

В приведенном ниже блоке кода показано создание процедуры — transfer(), которая принимает три параметра. Сразу после имени процедуры передаются аргументы с соответствующими типами данных — sourceAccountId, destinationAccountId, сумма. Процедура вычитает переданную сумму из одного account и добавляет ее к другому account.

language plpgsql
as $$
begin
    update accounts
    set balance = accounts.balance - amount
    where id = sourceAccountId;

    update accounts
    set balance = balance + amount
    where id = destinationAccountId;

    commit;
end;
$$;

Наконец, давайте применим все это на реальных примерах. Чтобы запустить весь код, который будет показан ниже, потребуется установить PostgreSQL (версия 13.2) на локальный компьютер или запустить PostgreSQL с помощью Docker-контейнера. Чтобы запустить PostgreSQL в Docker контейнере, необходимо запустить файл docker-compose.yaml, указанный ниже.

postgresqldb:
    container_name: database
    image: postgres:13.2-alpine
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_DB=customer-service
      - POSTGRES_USER=customer-dev
      - POSTGRES_PASSWORD=1awer321!qwQ
    volumes:
    - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
> docker-compose up

Давайте создадим таблицы users и purchases и заполним их. Для простоты в таблице users есть три столбца — id, name и profession; таблица profession состоит из четырех столбцов — id, name, cost и user_id.

CREATE table users
(
    id         serial primary key,
    name       varchar(255),
    profession varchar(255)
);

insert into users(name, profession)
values
('Bob', 'QA'),
('Camilo', 'Front End developer'),
('Billy', 'Backend Developer'),
('Alice', 'Mobile Developer'),
('Kate', 'QA'),
('Wayne', 'DevOps'),
('Tim', 'Mobile Developer'),
('Amigos', 'QA');

CREATE TABLE purchases
(
    id      serial primary key ,
    name    varchar(255),
    cost    numeric(10, 2),
    user_id int,
    foreign key (user_id)
        references users (id)
);

insert into purchases(name, cost, user_id)
values
('M1 MacBook Air', 1300.99, 1),
('Iphone 14', 1200.00 , 2),
('Iphon 10', 700.00, 3),
('Iphone 13', 800.00, 1),
('Intel Core i5', 500.00, 4),
('M1 MacBook Pro', 1500, 5),
('IMAC',2500 , 7),
('ASUS VIVOBOOK', 899.99, 6),
('Lenovo', 1232.99, 1),
('Galaxy S21', 999.99, 2),
('XIAMI REDMIBOOK 14', 742.99, 4),
('M1 MacBook Air', 1299.99 , 8),
('ACER', 799.99, 7);

После выполнения запросов мы можем проверить нашу базу данных.

Получение записей из таблицы users
Получение записей из таблицы users

Получение записей из таблицы purchases
Получение записей из таблицы purchases

Допустим, мы хотим запросить имя пользователя и его самые дорогие покупки. Можно написать несколько сложных запросов, которые вернут желаемый результат, однако мы можем использовать нашу функцию, определенную выше.

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

CREATE OR REPLACE FUNCTION findMostExpensivePurchase(customer_id int)
    RETURNS numeric(10, 2)
    LANGUAGE plpgsql
AS
$$
DECLARE
    itemCost numeric(10, 2);
begin
    SELECT MAX(cost)
    INTO itemCost
    FROM purchases
    WHERE user_id = customer_id;
    RETURN itemCost;
end;
$$;

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

SELECT findMostExpensivePurchase(1) as mostExpensivePurchase;

Получение самой дорогой покупки пользователя по id = 1
Получение самой дорогой покупки пользователя по id = 1

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

Получение списка имён пользователей и их самых дорогих покупок
Получение списка имён пользователей и их самых дорогих покупок

Как мы обсуждали ранее, хранимые процедуры немного отличаются от функций. Функции позволяют выполнять только Select-запросы, а хранимые процедуры позволяют выполнять Insert, Update, Delete операции. Хранимые процедуры очень удобны при работе со случаями, когда необходимы операции insert, update или delete.

Рассмотрим банковскую операцию — перевод. При выполнении какой-либо банковской операции деньги переводятся с одного счета на другой. Чтобы реализовать эту хранимую процедуру — transfer(), давайте создадим таблицу accounts и заполним ее.

create table accounts
(
    id      SERIAL primary key,
    balance  BIGINT,
    user_id INT unique ,
    FOREIGN KEY (user_id)
        references users (id)
);

INSERT INTO accounts(balance, user_id)
values
(1500, 1),
(1100, 2),
(2300, 3),
(7500, 5),
(6500, 4);

После выполнения приведенных выше запросов мы получим следующий вывод.

Получение записей из таблицы accounts
Получение записей из таблицы accounts

Для вызова хранимой процедуры используется — call procedure_name().

Сделаем условно перевод денег – 500 единиц с account.id = 3 на account.id = 4 и проверим результат.

Вызов функции transfer() и с последующим выводом записей из таблицы accounts
Вызов функции transfer() и с последующим выводом записей из таблицы accounts

Заключение

В этой статье мы рассмотрели:

  • что такое функции и хранимые процедуры, а также их разницу;
  • создание функции и процедуры;
  • применение функций и процедур на реальных примерах.
Похожее
Jul 26, 2023
Author: Jason Watmore
Built with .NET 7.0, Dapper 2.0 and PostgreSQL In this tutorial we'll show how to build a .NET 7.0 (ASP.NET Core) API with Dapper and PostgreSQL that supports CRUD operations. Tutorial contents Example API overview Tools required to run the...
May 9
Author: Michael Shpilt
In a previous blog post, I showed you how to use PostgreSQL in C# with Npgsql, Dapper, and Entity Framework Core. But if you’re going to use one of them, it’s probably a good idea to make sure you’re not...
Jan 11, 2023
Author: Nitesh Singhal
Database Setup with DbUp + Postgresql + Dapper in ASP.Net Core In this tutorial, we are going to explore how we can setup our database on startup when using Dapper for accessing database. When using Dapper, one of the key...
Jun 13, 2023
One of the things that we may struggle with as developers when working on a green field project is our stack. Choosing the right tech to solve a problem can be a harrowing experience. Databases, in particular, can be a...
Написать сообщение
Тип
Почта
Имя
*Сообщение