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

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

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

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

 

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

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

SELECT * FROM pg_tables;

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

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

 

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

SELECT * FROM pg_tables WHERE schemaname = 'b';

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

Получили список всех имён таблиц одной схемы в PostgreSQL
Получили список всех имён таблиц одной схемы в 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
Функция получения общего количества всех записей из таблиц схемы 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