PostgreSQL의 특정 값에 대해 모든 테이블의 모든 열 을 검색 할 수 있습니까?
Oracle에 대한 유사한 질문이 여기 에 있습니다 .
답변
데이터베이스의 내용을 덤프 한 다음 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');답변
다음은 열에 특정 값이 포함 된 레코드를 찾는 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)
JOIN information_schema.table_privileges p ON
(t.table_name=p.table_name AND t.table_schema=p.table_schema
AND p.privilege_type='SELECT')
JOIN information_schema.schemata s ON
(s.schema_name=t.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
FOR rowctid IN
EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
schemaname,
tablename,
columnname,
needle
)
LOOP
-- uncomment next line to get some progress report
-- RAISE NOTICE 'hit in %.%', schemaname, tablename;
RETURN NEXT;
END LOOP;
END LOOP;
END;
$$ language plpgsql;동일한 원칙을 기반으로하지만 속도와보고 개선 사항을 추가하는 github 의 버전 도 참조하십시오 .
테스트 데이터베이스에서의 사용 예 :
- 공개 스키마 내의 모든 테이블에서 검색 :
select * from search_columns ( 'foobar'); 스키마 이름 | tablename | columnname | rowctid ------------ + ----------- + ------------ + --------- 공개 | s3 | usename | (0,11) 공개 | s2 | relname | (7,29) 공개 | w | 몸 | (0,2) (3 열)
- 특정 테이블에서 검색 :
select * from search_columns ( 'foobar', '{w}');
스키마 이름 | tablename | columnname | rowctid
------------ + ----------- + ------------ + ---------
공개 | w | 몸 | (0,2)
(1 열)
- 선택에서 얻은 테이블 서브 세트에서 검색 :
select * from search_columns ( 'foobar', array (select table_name :: name from information_schema.tables where table_name like 's %'), array [ 'public']); 스키마 이름 | tablename | columnname | rowctid ------------ + ----------- + ------------ + --------- 공개 | s2 | relname | (7,29) 공개 | s3 | usename | (0,11) (2 열)
- 해당하는 기본 테이블 및 ctid가있는 결과 행을 가져옵니다.
선택 * from public.w where ctid = '(0,2)'; 제목 | 몸 | tsv ------- + -------- + --------------------- 토토 | 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)
답변
특정 값에 대해 모든 테이블의 모든 열을 검색합니다.
이것은 정확히 일치하는 방법을 정의하지 않습니다.
또한 정확히 무엇을 반환할지 정의하지 않습니다.
가정 :
- 주어진 값과 같지 않고 텍스트 표현에서 주어진 값을 포함 하는 열이있는 행을 찾습니다 .
- 가장 간단하기 때문에 테이블 이름 (
regclass)과 튜플 ID (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표현 의 행에 대한 구분 기호 및 데코레이터 가 검색 패턴의 일부가 될 수있는 경우 거짓 긍정이있을 수 있습니다.
- 열 구분 기호 :
,기본적으로 - 전체 행은 괄호로 묶여 있습니다.
() - 일부 값은 큰 따옴표로 묶여 있습니다.
" \이스케이프 문자로 추가 할 수 있습니다.
일부 열의 텍스트 표현은 로컬 설정에 따라 달라질 수 있지만 그 모호성은 내 솔루션이 아니라 질문에 내재되어 있습니다.
각 한정 행은 여러 번 일치하는 경우에도 한 번만 반환 됩니다 (여기의 다른 답변과 달리).
시스템 카탈로그를 제외한 전체 DB를 검색합니다. 일반적으로 완료하는 데 오랜 시간이 걸립니다 . 다른 답변에서 설명한 것처럼 특정 스키마 / 테이블 (또는 열)로 제한하고 싶을 수 있습니다. 또는 다른 답변에서 설명 된 알림과 진행률 표시기를 추가합니다.
regclass객체 식별자 타입은 테이블 이름 스키마 규정 필요한 전류에 따라 명확로서 표현된다 search_path:
무엇입니까 ctid?
검색 패턴에서 특별한 의미를 가진 문자를 이스케이프 할 수 있습니다. 보다:
답변
그리고 누군가 그것이 도움이 될 수 있다고 생각한다면. 다음은 @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;Bellow는 위에서 생성 한 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')
);답변
새 프로 시저를 저장하지 않고 코드 블록을 사용하고 실행하여 발생 테이블을 얻을 수 있습니다. 스키마, 테이블 또는 열 이름으로 결과를 필터링 할 수 있습니다.
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;답변
함수를 생성하거나 외부 도구를 사용하지 않고도이를 달성 할 수있는 방법이 있습니다. 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"}답변
진행률보고 기능이있는 @Daniel Vérité의 기능은 다음과 같습니다. 다음 세 가지 방법으로 진행 상황을보고합니다.
- RAISE NOTICE에 의해;
- 제공된 {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;