Сегодня мы изучим хранимые процедуры и функции:

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

Хранимая процедура — это набор SQL-операторов, которые могут быть выполнены как единое целое.

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL-операторы
END;

Выполнение хранимой процедуры:

EXEC procedure_name;

Функции:

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

CREATE FUNCTION function_name (@param1 INT, @param2 VARCHAR(50))
RETURNS INT
AS
BEGIN
    -- SQL-операторы
    RETURN some_value;
END;

Вызов функции:

SELECT dbo.function_name(param1, param2);

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

👉 @SQLPortal

Совет по Postgres: настройте префикс строки лога (log_line_prefix), чтобы при последующей фильтрации логов было больше контекста. По умолчанию там есть таймстамп и ID процесса, но можно добавить гораздо больше.

Например: user=%u, db=%d.

Новое в Postgres 18: %L — локальный IP-адрес сервера.

👉 @SQLPortal

Существует множество способов выполнить один и тот же запрос в Postgres.

- Делать поиск через последовательное сканирование (sequential scan) или по индексу?
- Какие индексы использовать?
- В каком порядке лучше джойнить таблицы?
- Какой алгоритм сортировки выбрать?

Все эти решения принимает query planner. Его задача — принять запрос, определить оптимальный план выполнения, а затем передать его в execution engine.

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

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

В таких случаях можно:

- настраивать параметры, например enable_seqscan=off
- вручную обновлять статистику
- или использовать расширения вроде pg_hint_plan, чтобы подсказать планировщику более подходящий вариант выполнения

👉 @SQLPortal

ORM обычно по умолчанию используют INSERT, но это не всегда лучший вариант — особенно если нужно загружать большие объёмы данных.

Для действительно крупных батчей стоит использовать COPY в PostgreSQL или \copy из командной строки.

Postgres: INSERT vs COPY

INSERT

- построчно (row by row), идеально для транзакций
- можно использовать ON CONFLICT
- подходит для нескольких тысяч строк, но COPY быстрее

COPY

- быстрее
- принцип «всё или ничего» (all-or-nothing)
- нужен для загрузки данных в больших объёмах
- можно запускать из psql через \copy

👉 @SQLPortal

Совет по PostgreSQL: \crosstabview

Если у вас есть результат из 3 столбцов, PostgreSQL может преобразовать его в сводную таблицу с помощью команды \crosstabview (в psql).

Для более сложных наборов данных используйте функцию crosstab()из расширения tablefunc — она требует более точной настройки с помощью SQL-запросов

👉 @SQLPortal

Нашёл коллекцию 100+ SQL-скриптов

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

Сохраняй если хочешь улучшить свои навыки работы с SQL. 👍

👉 @SQLPortal

Ищешь игры, которые помогут тебе выучить и практиковать SQL? 🤔

Попробуй SQL Noir — детективная игра по SQL

Пиши запросы, анализируй данные и раскрывай дела

Попробовать - https://www.sqlnoir.com/

👉 @SQLPortal

SQL-собеседования ОБОЖАЮТ проверять вас на оконные функции.

Вот список из 7 самых популярных оконных функций:

7 самых часто проверяемых оконных функций 👇

🟡RANK() – присваивает ранг каждой строке в разделе на основе указанного столбца или значения.
🟡DENSE_RANK() – присваивает ранг каждой строке, но не пропускает значения ранга.
🟡ROW_NUMBER() – присваивает каждой строке уникальный порядковый номер в разделе на основе порядка строк.
🟡LEAD() – извлекает значение из следующей строки в разделе на основе указанного столбца или выражения.
🟡LAG() – извлекает значение из предыдущей строки в разделе на основе указанного столбца или выражения.
🟡NTH_VALUE() – извлекает n-е значение в разделе.

👉 @SQLPortal

Изучайте SQL быстрее

Этот сайт объясняет ключевые понятия с помощью аналогий

- SQL, JavaScript, CSS, Git и многое другое

С интерактивными учебниками

→ http://codeanalogies.com

👉 @SQLPortal

Одна из N причин, почему шардинг — отличный способ масштабировать базу данных:

Скорость резервного копирования.

👉 @SQLPortal

Известная компания Postgres Professional выпустила свой обновлённый базовый курс DBA-1 по администрированию свободно распространяемой СУБД PostgreSQL.

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

Основные изменения:

- Добавлена информация про версии PostgreSQL 14, 15 и 16.
- Заменены четыре темы раздела «Управление доступом», по которым в дальнейшем появится отдельный подробный курс;
- Частично изменена структура: изложение стало более логичным и последовательным;
- Физическая и логическая репликации теперь рассматриваются в отдельных темах.
- Исправлены недочёты в изложении, ошибки в скриптах демонстраций и практических заданий.

Cам курс: тут

👉 @SQLPortal

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

Оригинальная англоязычная документация PostgreSQL и документация к СУБД Postgres Pro в вариантах Standard и Enterprise.

⛓ Ссылка: тык

👉 @SQLPortal

Игры, которые помогают изучать и практиковать SQL | Аналитика данных

Ссылки для доступа к играм:
1. SQL Island: https://sql-island.informatik.uni-kl.de/
2. SQL Murder Mystery: https://mystery.knightlab.com/
3. Полицейский департамент SQL: https://sqlpd.com/
4. The Schemaverse: https://schemaverse.com/

👉 @SQLPortal

Совет по Postgres: используйте pretty, чтобы видеть размеры таблиц в человекочитаемом формате; без него вы получите значения в байтах.

Общий размер таблицы, включая индексы и TOAST:

SELECT pg_size_pretty(pg_total_relation_size('my_table'));

32 kB

👉 @SQLPortal

Список с задачами для подготовки к SQL-интервью с различными базами данных:

1. [Database Basics](https://bit.ly/3zGK6S7)
2. [SQL Queries](https://bit.ly/3bGzN8r)
3. [MSSQL](https://bit.ly/3A5qIj0)
4. [MySQL](https://bit.ly/3bFkhtE)
5. [Postgres](https://bit.ly/3BOLLHP)
6. [Oracle](https://bit.ly/3zF2WsH)

👉 @SQLPortal

Функции pg_get_* — это встроенные функции интроспекции в Postgres.

pg_get_viewdef — «Покажи SQL, лежащий за этим представлением»

pg_get_indexdef — «Покажи CREATE INDEX для этого индекса»

pg_get_constraintdef — «Покажи, как определён этот внешний ключ / check-ограничение»

👉 @SQLPortal

Руководство по SQL

Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем

Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации

Сохраняйте, чтобы не потерять

👉 @SQLPortal

Вчера мы говорили о функциях pg_get_*. Чтобы получить отформатированный SQL, передайте true вместо одной длинной нечитаемой строки.

pg_get_viewdef — представления
pg_get_indexdef — индексы
pg_get_constraintdef — ограничения

SELECT pg_get_viewdef('active_orders'::regclass, true);

👉 @SQLPortal

Этот репозиторий содержит все, что вам нужно, чтобы прокачать свои навыки работы с SQL

Более 100 упражнений и примеров по SQL.

⛓ Ссылка: тык

👉 @SQLPortal

Перенаправляете вывод psql куда-то ещё? В psql есть настройки, позволяющие сократить вывод до одиночных строк данных.

psql -qtA

- -q — тихий режим
- -t — только строки (tuples only)
- -A — без выравнивания (unaligned)

👉 @SQLPortal