Можно ли искать в каждом столбце каждой таблицы конкретное значение в PostgreSQL?
Подобный вопрос доступен здесь для Oracle.
Как искать определенное значение во всех таблицах (PostgreSQL)?
Ответ 1
Как насчет сброса содержимого базы данных, а затем с помощью grep?
$ pg_dump --data-only --inserts -U postgres your-db-name > a.tmp
$ grep United a.tmp
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('GB', 'United Kingdom');
Та же самая утилита, pg_dump, может содержать имена столбцов в выводе. Просто измените --inserts на --column-inserts. Таким образом, вы также можете искать конкретные имена столбцов. Но если бы я искал имена столбцов, я бы, вероятно, сбросил схему вместо данных.
$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp
$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');
Ответ 2
Здесь функция pl/pgsql, которая находит записи, где любой столбец содержит определенное значение. Он принимает в качестве аргументов значение для поиска в текстовом формате, массив имен таблиц для поиска (по умолчанию для всех таблиц) и массив имен схем (по умолчанию для всех имен схем).
Он возвращает структуру таблицы со схемой, именем таблицы, именем столбца и псевдостолбцом ctid (недолговременное физическое расположение строки в таблице, см. Системные столбцы)
CREATE OR REPLACE FUNCTION search_columns(
needle text,
haystack_tables name[] default '{}',
haystack_schema name[] default '{}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
FOR schemaname,tablename,columnname IN
SELECT c.table_schema,c.table_name,c.column_name
FROM information_schema.columns c
JOIN information_schema.tables t ON
(t.table_name=c.table_name AND t.table_schema=c.table_schema)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
AND t.table_type='BASE TABLE'
LOOP
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
END IF;
END LOOP;
END;
$$ language plpgsql;
РЕДАКТИРОВАТЬ: этот код для PG 9.1 или новее. Кроме того, вам может потребоваться версия для github, основанная на том же принципе, но с некоторыми улучшениями в скорости и отчетности.
Примеры использования в тестовой базе данных:
- Поиск во всех таблицах общедоступной схемы:
select * from search_columns('foobar');
schemaname | tablename | columnname | rowctid
------------+-----------+------------+---------
public | s3 | usename | (0,11)
public | s2 | relname | (7,29)
public | w | body | (0,2)
(3 rows)
- Поиск в конкретной таблице:
select * from search_columns('foobar','{w}');
schemaname | tablename | columnname | rowctid
------------+-----------+------------+---------
public | w | body | (0,2)
(1 row)
- Поиск в подмножестве таблиц, полученных из выбора:
select * from search_columns('foobar', array(select table_name::name from information_schema.tables where table_name like 's%'), array['public']);
schemaname | tablename | columnname | rowctid
------------+-----------+------------+---------
public | s2 | relname | (7,29)
public | s3 | usename | (0,11)
(2 rows)
- Получить строку результата с соответствующей базовой таблицей и и ctid:
select * from public.w where ctid='(0,2)'; title | body | tsv -------+--------+--------------------- toto | foobar | 'foobar':2 'toto':1
Варианты
Чтобы снова проверить регулярное выражение вместо строгого равенства, например, grep, эту часть запроса:
SELECT ctid FROM %I.%I WHERE cast(%I as text)=%Lможет быть изменено на:
SELECT ctid FROM %I.%I WHERE cast(%I as text) ~ %LДля сравнения без учета регистра вы можете написать:
SELECT ctid FROM %I.%I WHERE lower(cast(%I as text)) = lower(%L)
Ответ 3
Единственный известный мне инструмент, который может это сделать: SQL Workbench/J: http://www.sql-workbench.net/
Инструмент на основе Java/JDBC, который предлагает специальную (проприетарную) SQL-команду для поиска по всем (или только выбранным) таблицам в базе данных:
http://www.sql-workbench.eu/manual/wb-commands.html#command-search-data
http://www.sql-workbench.eu/wbgrepdata_png.html
Ответ 4
И если кто-то подумает, что это может помочь. Вот функция @Daniel Vérité с другим параметром, который принимает имена столбцов, которые можно использовать в поиске. Таким образом, это сокращает время обработки. По крайней мере, в моем тесте он значительно сократился.
CREATE OR REPLACE FUNCTION search_columns(
needle text,
haystack_columns name[] default '{}',
haystack_tables name[] default '{}',
haystack_schema name[] default '{public}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
FOR schemaname,tablename,columnname IN
SELECT c.table_schema,c.table_name,c.column_name
FROM information_schema.columns c
JOIN information_schema.tables t ON
(t.table_name=c.table_name AND t.table_schema=c.table_schema)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND c.table_schema=ANY(haystack_schema)
AND (c.column_name=ANY(haystack_columns) OR haystack_columns='{}')
AND t.table_type='BASE TABLE'
LOOP
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
END IF;
END LOOP;
END;
$$ language plpgsql;
Ниже приведен пример использования созданной выше функции search_function.
SELECT * FROM search_columns('86192700'
, array(SELECT DISTINCT a.column_name::name FROM information_schema.columns AS a
INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
WHERE
a.column_name iLIKE '%cep%'
AND b.table_type = 'BASE TABLE'
AND b.table_schema = 'public'
)
, array(SELECT b.table_name::name FROM information_schema.columns AS a
INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
WHERE
a.column_name iLIKE '%cep%'
AND b.table_type = 'BASE TABLE'
AND b.table_schema = 'public')
);
Ответ 5
Без сохранения новой процедуры вы можете использовать блок кода и выполнить для получения таблицы вступлений. Вы можете фильтровать результаты по имени схемы, таблицы или столбца.
DO $$
DECLARE
value int := 0;
sql text := 'The constructed select statement';
rec1 record;
rec2 record;
BEGIN
DROP TABLE IF EXISTS _x;
CREATE TEMPORARY TABLE _x (
schema_name text,
table_name text,
column_name text,
found text
);
FOR rec1 IN
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE table_name <> '_x'
AND UPPER(column_name) LIKE UPPER('%%')
AND table_schema <> 'pg_catalog'
AND table_schema <> 'information_schema'
AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
LOOP
sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%my_substring_to_find_goes_here%' , ''')');
RAISE NOTICE '%', sql;
BEGIN
FOR rec2 IN EXECUTE sql LOOP
RAISE NOTICE '%', sql;
INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
END LOOP;
EXCEPTION WHEN OTHERS THEN
END;
END LOOP;
END; $$;
SELECT * FROM _x;
Ответ 6
искать в каждом столбце каждой таблицы определенное значение
Это не определяет, как точно соответствовать.
Он также не определяет, что именно вернуть.
Предполагая, что:
- Найти любую строку с любым столбцом, содержащим заданное значение в его текстовом представлении, в отличие от выравнивания заданного значения.
- Верните имя таблицы (
regclass) и указатель элемента (ctid), потому что этоctidпростое.
Вот чертовски простой, быстрый и немного грязный способ:
CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
RETURNS TABLE(_tbl regclass, _ctid tid) AS
$func$
BEGIN
FOR _tbl IN
SELECT c.oid::regclass
FROM pg_class c
JOIN pg_namespace n ON n.oid = relnamespace
WHERE c.relkind = 'r' -- only tables
AND n.nspname !~ '^(pg_|information_schema)' -- exclude system schemas
ORDER BY n.nspname, c.relname
LOOP
RETURN QUERY EXECUTE format(
'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
, _tbl, '%' || _like_pattern || '%')
USING _tbl;
END LOOP;
END
$func$ LANGUAGE plpgsql;
Вызов:
SELECT * FROM search_whole_db('mypattern');
Укажите шаблон поиска без вложения %.
Почему слегка грязный?
Если разделители и декораторы для строки в text представлении могут быть частью шаблона поиска, могут быть ложные срабатывания:
- Колонка разделитель:
,по умолчанию - вся строка заключена в круглые скобки:
() - некоторые значения заключены в двойные кавычки
" -
\может быть добавлено как escape-символ
И текстовое представление некоторых столбцов может зависеть от локальных настроек - но эта неоднозначность присуща вопросу, а не моему решению.
Каждая соответствующая строка возвращается только один раз, даже если она совпадает несколько раз (в отличие от других ответов здесь).
Это ищет всю БД, за исключением системных каталогов. Обычно это займет много времени, чтобы закончить. Возможно, вы захотите ограничиться определенными схемами/таблицами (или даже столбцами), как показано в других ответах. Или добавьте уведомления и индикатор прогресса, также продемонстрированный в другом ответе.
Тип идентификатора объекта regclass представлен в виде имени таблицы, при необходимости search_path схеме для устранения неоднозначности в соответствии с текущим search_path:
Что такое ctid?
Возможно, вы захотите экранировать символы с особым значением в шаблоне поиска. Увидеть:
Ответ 7
Есть способ достичь этого без создания функции или использования внешнего инструмента. Используя функцию query_to_xml() Postgres, которая может динамически выполнять запрос внутри другого запроса, можно искать текст во многих таблицах. Это основано на моем ответе для получения количества строк для всех таблиц:
Для поиска строки foo во всех таблицах схемы можно использовать следующее:
with found_rows as (
select format('%I.%I', table_schema, table_name) as table_name,
query_to_xml(format('select to_jsonb(t) as table_row
from %I.%I as t
where t::text like ''%%foo%%'' ', table_schema, table_name),
true, false, '') as table_rows
from information_schema.tables
where table_schema = 'public'
)
select table_name, x.table_row
from found_rows f
left join xmltable('//table/row'
passing table_rows
columns
table_row text path 'table_row') as x on true
Обратите внимание, что для использования xmltable требуется Postgres 10 или новее. Для более старой версии Postgres это также можно сделать с помощью xpath().
with found_rows as (
select format('%I.%I', table_schema, table_name) as table_name,
query_to_xml(format('select to_jsonb(t) as table_row
from %I.%I as t
where t::text like ''%%foo%%'' ', table_schema, table_name),
true, false, '') as table_rows
from information_schema.tables
where table_schema = 'public'
)
select table_name, x.table_row
from found_rows f
cross join unnest(xpath('/table/row/table_row/text()', table_rows)) as r(data)
Общее табличное выражение (WITH...) используется только для удобства. Он перебирает все таблицы в public схеме. Для каждой таблицы следующий запрос выполняется с помощью функции query_to_xml():
select to_jsonb(t)
from some_table t
where t::text like '%foo%';
Предложение where используется для того, чтобы дорогая генерация содержимого XML выполнялась только для строк, содержащих строку поиска. Это может вернуть что-то вроде этого:
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<table_row>{"id": 42, "some_column": "foobar"}</table_row>
</row>
</table>
Преобразование всей строки в jsonb выполняется, чтобы в результате можно было увидеть, какое значение принадлежит какому столбцу.
Выше может вернуть что-то вроде этого:
table_name | table_row
-------------+----------------------------------------
public.foo | {"id": 1, "some_column": "foobar"}
public.bar | {"id": 42, "another_column": "barfoo"}
Ответ 8
Здесь функция @Daniel Vérité функционирует с функцией отчетов о ходе выполнения. Он сообщает о прогрессе тремя способами:
- УВЕДОМЛЕНИЕ ОБ ИЗМЕНЕНИИ ПОДРОБНОСТИ;
- путем уменьшения значения поставленной последовательности {progress_seq} из {общее количество столбцов для поиска} до 0;
- записывая прогресс вместе с найденными таблицами в текстовый файл, расположенный в c:\windows\temp\{progress_seq}.txt.
_
CREATE OR REPLACE FUNCTION search_columns(
needle text,
haystack_tables name[] default '{}',
haystack_schema name[] default '{public}',
progress_seq text default NULL
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
DECLARE
currenttable text;
columnscount integer;
foundintables text[];
foundincolumns text[];
begin
currenttable='';
columnscount = (SELECT count(1)
FROM information_schema.columns c
JOIN information_schema.tables t ON
(t.table_name=c.table_name AND t.table_schema=c.table_schema)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND c.table_schema=ANY(haystack_schema)
AND t.table_type='BASE TABLE')::integer;
PERFORM setval(progress_seq::regclass, columnscount);
FOR schemaname,tablename,columnname IN
SELECT c.table_schema,c.table_name,c.column_name
FROM information_schema.columns c
JOIN information_schema.tables t ON
(t.table_name=c.table_name AND t.table_schema=c.table_schema)
WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
AND c.table_schema=ANY(haystack_schema)
AND t.table_type='BASE TABLE'
LOOP
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
) INTO rowctid;
IF rowctid is not null THEN
RETURN NEXT;
foundintables = foundintables || tablename;
foundincolumns = foundincolumns || columnname;
RAISE NOTICE 'FOUND! %, %, %, %', schemaname,tablename,columnname, rowctid;
END IF;
IF (progress_seq IS NOT NULL) THEN
PERFORM nextval(progress_seq::regclass);
END IF;
IF(currenttable<>tablename) THEN
currenttable=tablename;
IF (progress_seq IS NOT NULL) THEN
RAISE NOTICE 'Columns left to look in: %; looking in table: %', currval(progress_seq::regclass), tablename;
EXECUTE 'COPY (SELECT unnest(string_to_array(''Current table (column ' || columnscount-currval(progress_seq::regclass) || ' of ' || columnscount || '): ' || tablename || '\n\nFound in tables/columns:\n' || COALESCE(
(SELECT string_agg(c1 || '/' || c2, '\n') FROM (SELECT unnest(foundintables) AS c1,unnest(foundincolumns) AS c2) AS t1)
, '') || ''',''\n''))) TO ''c:\WINDOWS\temp\' || progress_seq || '.txt''';
END IF;
END IF;
END LOOP;
END;
$$ language plpgsql;
Ответ 9
- Ниже функция выведет список всех таблиц, которые содержат конкретную строку в базе данных.
select TablesCount(‘StringToSearch);
--Iterates через все таблицы в базе данных
CREATE OR REPLACE FUNCTION **TablesCount**(_searchText TEXT)
RETURNS text AS
$$ -- here start procedural part
DECLARE _tname text;
DECLARE cnt int;
BEGIN
FOR _tname IN SELECT table_name FROM information_schema.tables where table_schema='public' and table_type='BASE TABLE' LOOP
cnt= getMatchingCount(_tname,Columnames(_tname,_searchText));
RAISE NOTICE 'Count% ', CONCAT(' ',cnt,' Table name: ', _tname);
END LOOP;
RETURN _tname;
END;
$$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification
- Возвращает количество таблиц, для которых выполняется условие. - Например, если нужный текст существует в любом из полей таблицы, - тогда счет будет больше 0. Мы можем найти уведомления - в разделе "Сообщения" средства просмотра результатов в базе данных postgres.
CREATE OR REPLACE FUNCTION **getMatchingCount**(_tname TEXT, _clause TEXT)
RETURNS int AS
$$
Declare outpt text;
BEGIN
EXECUTE 'Select Count(*) from '||_tname||' where '|| _clause
INTO outpt;
RETURN outpt;
END;
$$ LANGUAGE plpgsql;
--Get поля каждой таблицы. Создает предложение where со всеми столбцами таблицы.
CREATE OR REPLACE FUNCTION **Columnames**(_tname text,st text)
RETURNS text AS
$$ -- here start procedural part
DECLARE
_name text;
_helper text;
BEGIN
FOR _name IN SELECT column_name FROM information_schema.Columns WHERE table_name =_tname LOOP
_name=CONCAT('CAST(',_name,' as VarChar)',' like ','''%',st,'%''', ' OR ');
_helper= CONCAT(_helper,_name,' ');
END LOOP;
RETURN CONCAT(_helper, ' 1=2');
END;
$$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification