PostgreSQL | Сколько всего записей имеется у всех таблиц в одной схеме данных?

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

При таком подходе всегда понятно, что там лежит. Остаётся только одно — а сколько «этого» там лежит? Много или мало? Как посчитать?

Как узнать суммарное количество записей всех таблиц одной схемы данных в PostgreSQL?

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

SELECT * FROM pg_tables;

Данная команда выведет все возможные имена таблиц, схем и их владельцев в ТЕКУЩЕЙ базе данных в виде таблицы.

Фрагмент из таблицы pg_tables в PostgreSQL с именами схем и таблиц

Теперь нужно просто отфильтровать часть результатов по признаку схемы данных. Пусть в нашем случае это будет схема «b«.

SELECT * FROM pg_tables WHERE schemaname = 'b';

Данная команда выведет все возможные имена таблиц одной схемы

Получили список всех имён таблиц одной схемы в PostgreSQL

В нашем случае получилось 18 таблиц. То есть 18 имён таблиц.

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

— Функция получения общего количества всех записей из таблиц схемы b

— Без получения массива из всех имён. Обычный последовательный перебор циклом.

— DROP FUNCTION a.get_all_rows_in_b_scheme()

— SELECT a.get_all_rows_in_b_scheme()

CREATE OR REPLACE FUNCTION a.get_all_rows_in_b_scheme()

RETURNS TABLE (summary bigint)

LANGUAGE plpgsql

AS $$

DECLARE

accum bigint := 0; — аккумулятор, накапливающий сумму количеств

scheme text := ‘b’;

z bigint := (SELECT count(*) FROM pg_tables WHERE schemaname = scheme);

y text; — перезаписываемый

item bigint;

BEGIN

FOR x IN 1..z

LOOP

EXECUTE ‘SELECT tablename FROM pg_tables WHERE schemaname = »’||scheme||»’ LIMIT 1 OFFSET ‘||x||‘-1’ INTO y;

EXECUTE ‘SELECT count(*) FROM b.’||y||» INTO item;

accum := accum + item;

END LOOP;

RETURN QUERY SELECT accum;

END;

— Экранирование строк

— https://postgrespro.ru/docs/postgresql/14/plpgsql-development-tips

Внутри функции объявляется несколько вспомогательных переменных для перелива данных туда-сюда:

accum — имеет стартовое значение 0 (ноль записей) и по мере работы цикла FOR прибавляет сама к себе количество записей по каждой таблице

scheme — название схемы данных, по которой нужно собрать суммарное количество записей

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

y — каждую итерацию цикла перезаписывается и хранит в себе название таблицы

item — каждую итерацию цикла перезаписывается и хранит в себе количество записей текущей выбранной таблицы

Данная функция вернёт целое число найденного количества записей во всех таблицах схемы «b«.

Функция получения общего количества всех записей из таблиц схемы b в PostgreSQL

Задача выполнена. Итоговое число можно использовать в дальнейшей логике работы приложения или самой СУБД.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Раздел «Глава 52. Системные каталоги» — https://postgrespro.ru/docs/postgresql/14/catalogs

Раздел «52.92. pg_tables» — https://postgrespro.ru/docs/postgresql/14/view-pg-tables