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

Question

В базе много таблиц. Мне нужно найти определенную и я не знаю как она называется, но я знаю одно из значений в ее столбцах. Можно ли как-то найти нужную таблицу?

Answer 1

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

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

Answer 2

Попробуйте функцию:

CREATE OR REPLACE FUNCTION find_tab(my_value TEXT) 
RETURNS TEXT AS $$
DECLARE
    tab_list TEXT[];
    cur REFCURSOR;
    cur_col REFCURSOR;
    row_data TEXT[];
    row_data_col TEXT[];
    val TEXT;
    i int;
    table_name TEXT;
BEGIN
    --Шаг1: выбираем все таблицы
    SELECT '{'|| string_agg('"'||tablename||'"', ', ')||'}'
    FROM pg_tables
    INTO tab_list;
    
    FOREACH table_name IN ARRAY tab_list LOOP
        BEGIN
            --Шаг2. Выбираем все данные (возможно, можно оптимальнее)
            OPEN cur FOR EXECUTE format('SELECT array_agg((SELECT array_agg(CASE WHEN value IS NULL THEN %L ELSE value END) FROM json_each_text(row_to_json(tbl_row)))) FROM %I AS tbl_row;','_', table_name);
            LOOP
                FETCH cur INTO row_data;
                EXIT WHEN NOT FOUND;
                i = 0;
                FOREACH val IN ARRAY row_data LOOP
                    IF val = my_value THEN
                        CLOSE cur;
                        -- Шаг3. Если значение найдено, проверяем кроме таблицы еще и колонку.
                        OPEN cur_col FOR EXECUTE format('SELECT array_agg(column_name::TEXT ORDER BY ordinal_position) FROM information_schema.columns WHERE table_name = %L;', table_name);
                        FETCH cur_col INTO row_data_col;
                        RETURN 'Таблица '||table_name || ' колонка '||row_data_col[i%array_length(row_data_col, 1)+1];
                    END IF;
                    i = i + 1;
                END LOOP;
            END LOOP;
            CLOSE cur;
        EXCEPTION WHEN OTHERS THEN
        -- Пропускаем таблицы, к которым нет доступа или которые нельзя прочитать
        CONTINUE;
        END;
    END LOOP;
    RETURN 'Таблица не найдена!';
END;
$$ LANGUAGE plpgsql;

Использование:

SELECT find_tab('oid');

Обновление: Внесла небольшое исправление в поиск колонок - "column_name::TEXT ORDER BY ordinal_position" и еще обнаружила, что индексация в pg с единицы.

БЛОГ НА HUSL

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

Ответы (2 шт):