Поиск строки во всей базе PostgreSQL

Создаем

CREATE OR REPLACE FUNCTION find_text_in_db(p_text text)
    RETURNS TABLE(table_schema text, table_name text, column_name text, row_data jsonb)
    LANGUAGE plpgsql
AS $$
DECLARE
    r record;
    sql text;
    where_clause text;
BEGIN
    FOR r IN
        SELECT
            c.table_schema,
            c.table_name,
            c.column_name,
            c.data_type
        FROM information_schema.columns AS c
        WHERE c.table_schema NOT IN ('pg_catalog', 'information_schema')
        LOOP
            -- Выбираем WHERE в зависимости от типа
            IF r.data_type IN ('text', 'character varying', 'character', 'varchar', 'char') THEN
                where_clause := format(
                        'WHERE t.%I::text LIKE %L',
                        r.column_name,
                        '%' || p_text || '%'
                                );
            ELSE IF r.data_type IN ('json', 'jsonb') THEN
                where_clause := format(
                        'WHERE t.%I::text LIKE %L',
                        r.column_name,
                        '%' || p_text || '%'
                                );
            ELSE
                -- Пропускаем не текстовые типы
                CONTINUE;
            END IF;
            END IF;

            sql := format(
                    'SELECT %L, %L, %L, to_jsonb(t) FROM %I.%I t %s',
                    r.table_schema,
                    r.table_name,
                    r.column_name,
                    r.table_schema,
                    r.table_name,
                    where_clause
                   );

            RETURN QUERY EXECUTE sql;
        END LOOP;
END $$;

SELECT * FROM find_text_in_db('needle');