Как выполнить SELECT из таблиц, имена которых выбираются динамически в PostgreSQL

При работе с базами данных 1С на платформе PostgreSQL администраторы и разработчики часто сталкиваются с необходимостью массовой обработки таблиц. Одной из типичных задач является поиск записей (например, неактивных записей в регистрах накопления _accumrg), когда точные имена таблиц заранее неизвестны или их слишком много для ручного перебора. В этой статье мы разберем, почему стандартный SELECT не работает с подзапросами в секции FROM, и рассмотрим эффективные способы решения этой задачи.

Почему нельзя просто написать SELECT FROM (SELECT name...)

Разберем типичную ошибку начинающих. Попытка выполнить запрос вида SELECT * FROM (SELECT table_name FROM information_schema.tables...) приводит к тому, что внешний запрос пытается прочитать данные из результата подзапроса, который является списком строк (имен), а не самими таблицами. В SQL идентификаторы таблиц должны быть жестко определены на этапе компиляции запроса. Система не может автоматически "подставить" строковое значение из ячейки таблицы как ссылку на другой объект базы данных.

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

Способ 1. Использование анонимного блока DO и динамического выполнения EXECUTE

Проанализируем самый мощный способ, который позволяет выполнить задачу "одним куском кода" прямо в консоли запросов. Мы воспользуемся процедурным расширением PL/pgSQL. Основная идея заключается в том, чтобы программно сформировать строку запроса и выполнить её командой EXECUTE.

Рассмотрим по шагам реализацию этого метода:

1. Создаем временную таблицу для сбора результатов.
2. Запускаем цикл по системному каталогу information_schema.tables.
3. Внутри цикла проверяем наличие нужной колонки (например, _active), чтобы запрос не упал с ошибкой на таблицах другой структуры.
4. Генерируем текст запроса и выполняем его, вставляя данные во временную таблицу.

Посмотрим на пример реализации:

DO $$
DECLARE
    row_record RECORD;
    query_text TEXT;
BEGIN
    -- Создаем временную таблицу для хранения найденных записей
    CREATE TEMP TABLE IF NOT EXISTS found_inactive_records (
        table_name_source TEXT,
        record_id BYTEA, -- Тип данных зависит от вашей конфигурации 1С
        period TIMESTAMP
    ) ON COMMIT DROP;

    -- Цикл по всем таблицам регистров накопления
    FOR row_record IN 
        SELECT t.table_name 
        FROM information_schema.tables t
        JOIN information_schema.columns c ON t.table_name = c.table_name
        WHERE t.table_name LIKE '_accumrg%' 
          AND c.column_name = '_active'
          AND t.table_schema = 'public'
    LOOP
        -- Формируем динамический запрос
        query_text := format('INSERT INTO found_inactive_records ' ||
                            'SELECT %L, _idrref, _period FROM %I WHERE _active = false', 
                            row_record.table_name, row_record.table_name);
        
        -- Выполняем сформированную строку
        EXECUTE query_text;
    END LOOP;
END $$;

-- Выводим накопленные результаты
SELECT * FROM found_inactive_records;

В данном коде функция format() с параметром %I безопасно экранирует имя таблицы, защищая от SQL-инъекций, а %L вставляет имя таблицы как литерал (строку) для идентификации источника данных.

Способ 2. Использование утилиты psql и команды \gexec

Выясним, как решить задачу еще быстрее, если у вас есть доступ к стандартному терминалу psql. В этой утилите существует уникальная команда \gexec, которая берет результат предыдущего запроса и выполняет каждую его строку как отдельную SQL-команду.

Разберем этот алгоритм:

1. Пишем запрос, который генерирует текст других запросов SELECT.
2. Завершаем команду мета-командой \gexec.

Пример команды:

SELECT format('SELECT %L as source, * FROM %I WHERE _active = false;', table_name, table_name)
FROM information_schema.tables 
WHERE table_name LIKE '_accumrg%'
\gexec

Это превращает результат в серию последовательно выполняемых выборок. Это идеальный вариант для быстрой диагностики базы данных "на лету".

Способ 3. Сборка единого запроса через UNION ALL (статический подход)

Если автоматизация через DO-блок кажется слишком сложной или база данных имеет строгие ограничения на выполнение процедурного кода, рассмотрим метод "подготовки запроса". Этот подход часто используется разработчиками 1С: сначала мы получаем список таблиц, а затем программно (на языке 1С или в текстовом редакторе) формируем один гигантский запрос с объединением UNION ALL.

Проанализируем ситуацию: поскольку таблицы регистров накопления в 1С имеют разные наборы измерений и ресурсов, мы не можем сделать SELECT *. Нам нужно выбирать только общие поля, которые гарантированно есть во всех таких таблицах (например, _period, _active, _lineno).

Пример итогового запроса:

SELECT '_accumrg123' as tablename, _period, _active FROM _accumrg123 WHERE _active = false
UNION ALL
SELECT '_accumrg456' as tablename, _period, _active FROM _accumrg456 WHERE _active = false
UNION ALL
SELECT '_accumrg789' as tablename, _period, _active FROM _accumrg789 WHERE _active = false;

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

SELECT relname 
FROM pg_class c 
JOIN pg_namespace n ON n.oid = c.relnamespace 
WHERE relkind = 'r' AND relname LIKE '_accumrg%';

Особенности работы с данными 1С в PostgreSQL

При выполнении подобных запросов следует учитывать специфику хранения данных 1С:

• Имена таблиц: Соответствие между метаданными (например, "РегистрНакопления.Продажи") и физическим именем (_accumrg100) хранится в системных таблицах 1С (таких как Config или DBSchema). Если вам нужно найти данные конкретного регистра, лучше сначала определить его имя через консоль запросов 1С.
• Типы данных: Поля ссылок в 1С имеют тип BYTEA. При выводе их в SQL-консоль они могут выглядеть нечитаемо. Для удобства можно использовать функцию encode(_idrref, 'hex').
• Производительность: Массовый опрос сотен таблиц может создать нагрузку на дисковую подсистему. Старайтесь ограничивать выборку по дате (поле _period), если это возможно.

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