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

Когда ты работаешь с любой SQL базой данных, ты можешь заметить, что одни и те же SELECT запросы используются очень часто. Они бывают короткими, тогда их несложно писать с нуля.

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

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

Сохраняем простой SELECT запрос как хранимую процедуру

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

Если тебе нужно получить количество пользователей твоего приложения, ты напишешь что-то вроде:

SELECT count(*) FROM app_user;

Чтобы дать имя этому SQL запросу, ты можешь сохранить его как stored procedure.

create or replace function count_app_users()
    returns table
            (
                total bigint
            )
    language plpgsql
as
$$
begin
    return query (
      select count(*) from app_user
    );
end;
$$;

Давай разберем пример по шагам:

  • В первой строке мы создаем функцию и называем ее count_app_users.
  • Команда create or replace используется для того, чтобы создать функцию, если она не существует, или обновить ее, если она уже есть. Если ты уверен в том, что функции еще нет, можешь использовать обычный create
  • Дальше мы явно указываем тип результата. Она равен table для всех SELECT запросов.
  • В скобках мы указываем столбцы итоговой таблицы и их тип.
  • Команду language plpgsql и несколько следующих строк ты можешь рассматривать как шаблон и не слишком вникать в них
  • Сам запрос мы помещаем внутрь выражения return query(...) которое обернуто в $$ begin и end $$

Вызываем хранимую процедуру

После того, как мы сохранили SELECT запрос, мы можем вызвать его.

SELECT * FROM count_app_users();

Такой синтаксис получается потому, что процедура возвращает таблицу. Чтобы получить все ее строки мы выполняем обычный SELECT *

Передаем аргументы в хранимую процедуру PostgreSQL

Дальше, представим, что мы хотим посчитать только пользователей созданных после определенной даты.

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

create or replace function count_app_users(after timestamp) -- declaring the "after" parameter
    returns table
            (
                total bigint
            )
    language plpgsql
as
$$
begin
    return query (
      select count(*) from app_user
      where created_at > after                              -- using the "after" parameter
    );
end;
$$;

Теперь мы можем передать дату в процедуру count_app_users и получить количество всех пользователей созданных после этой даты.

SELECT * FROM count_app_users('2020-11-01');

Удаление хранимой процедуры PostgreSQL

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

И у нас получилось две функции. Первая — count_app_users, вторая — count_app_users(after). Обе они отлично живут в SQL базе данных.

Если одна из них тебе не нужна, то ее можно удалить так:

DROP function count_app_users();

Синтаксис похож на удаление таблицы и в нем нет каких-то подводных камней.

Посмотреть текст хранимой процедуры PostgreSQL

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

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

SELECT prosrc
FROM pg_proc
WHERE proname = 'count_app_users';

Такой запрос вернет нам все, что мы писали между begin и end при создании.

Разбираемся с частыми ошибками

Первая ошибка с которой ты можешь столкнуться при сохранении SELECT запроса выглядит примерно так:

ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Where: PL/pgSQL function count_app_users() line 3 at SQL statement

Она возникает если ты не оберешь SELECT запрос в выражение return query(...).

Дальше, может быть ошибка связанная с неправильным вызовом хранимой процедуры в PostgreSQL:

ERROR: cannot use RETURN QUERY in a non-SETOF function

Скорее всего ты попробовал вызывать функцию так:

call count_app_users();

Или так:

SELECT count_app_users();

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

SELECT * FROM count_app_users();

Заключение

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

Мы разобрали очень простой пример, но ты можешь сохранять SELECT запросы любой сложности.

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