programing

모든 테이블(PostgreSQL)에서 특정 값을 검색하는 방법은 무엇입니까?

topblog 2023. 5. 8. 21:47
반응형

모든 테이블(PostgreSQL)에서 특정 값을 검색하는 방법은 무엇입니까?

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 버전도 참조하십시오.

테스트 데이터베이스에서의 사용 예:

  • 공용 스키마 내의 모든 테이블에서 검색:
search_search foobar'에서 *를 선택합니다.;
스키마 이름 | 테이블 이름 | 열 이름 | rowctid------------+-----------+------------+---------public | s3 | 사용자 이름 | (0,11)공용 | s2 | relname | (7,29)공공 | w | 본문 | (0,2)(3열)
  • 특정 테이블에서 검색:
search_search foobar', '{w}'에서 *를 선택합니다.;
스키마 이름 | 테이블 이름 | 열 이름 | rowctid------------+-----------+------------+---------공공 | w | 본문 | (0,2)(1행)
  • 선택 항목에서 얻은 테이블의 하위 집합에서 검색:
search_foobar'에서 *를 선택하고, array(information_property.foobar에서 table_name::name을 선택합니다. 여기서 table_name은 's%'와 같은 경우), array['public']);스키마 이름 | 테이블 이름 | 열 이름 | rowctid------------+-----------+------------+---------공용 | s2 | relname | (7,29)public | s3 | 사용자 이름 | (0,11)(2열)
  • 해당하는 기본 테이블과 ctid가 있는 결과 행을 가져옵니다.
공개에서 *를 선택합니다.w ctid='(0,2)';제목 | 본문 | tsv-------+--------+---------------------토토 | foobar | 'foobar':2 'foobar':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 () 및 튜 플 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');

▁encl를 동봉하지 않고 검색 패턴을 합니다.%.

왜 약간 더러워요?

행대구한기분및경장우인자의 ,text표현은 검색 패턴의 일부가 될 수 있으며 잘못된 긍정이 있을 수 있습니다.

  • 구분 기호: 열 구 기 호분,
  • 전체 행은 괄호로 묶습니다.()
  • 은 큰따옴표로 ."
  • \이스케이프 문자로 추가할 수 있습니다.

그리고 일부 열의 텍스트 표현은 지역 설정에 따라 달라질 수 있지만, 그 모호성은 질문에 내재되어 있습니다. 제 해결책이 아닙니다.

각 한정된 행은 여러 번 일치하는 경우에도 한 번만 반환됩니다(여기의 다른 답변과는 대조적).

시스템 카탈로그를 제외한 전체 DB를 검색합니다.일반적으로 완료하는 데 시간이 오래 걸립니다.다른 답변에 나와 있는 것처럼 특정 스키마/테이블(또는 열)로 제한할 수 있습니다.또는 다른 답변에서 설명한 공지사항과 진행률 표시기를 추가합니다.

그regclass이름으로 되며, 개식 유자테이표로며현, 따명구경필한위다니됩정에 할 수 있는 스키마 이 부여됩니다.search_path:

무입니까는 입니까?ctid?

검색 패턴에서 특별한 의미를 가진 문자를 이스케이프할 수 있습니다.참조:

함수를 만들거나 외부 도구를 사용하지 않고도 이를 달성할 수 있는 방법이 있습니다. Postgres'를 사용하여query_to_xml()다른 쿼리 내에서 쿼리를 동적으로 실행할 수 있는 함수로, 여러 테이블에서 텍스트를 검색할 수 있습니다.이는 모든 테이블에 대한 행 수를 검색하기 위한 내 답변을 기반으로 합니다.

" " " 를 과 같이 하십시오.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

의사에주시오십의하용▁▁use를 사용하는 것에 하세요.xmltablePostgres 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"}

Postgres 10+에 대한 온라인 예제

이전 Postgres 버전의 온라인 예제

새 프로시저를 저장하지 않고 코드 블록을 사용하고 실행하여 발생 테이블을 얻을 수 있습니다.스키마, 테이블 또는 열 이름을 기준으로 결과를 필터링할 수 있습니다.

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;

IntelliJ를 사용하는 경우 데이터베이스 보기에 DB를 추가한 후 데이터베이스를 마우스 오른쪽 단추로 누르고 전체 텍스트 검색을 선택하면 특정 텍스트에 대한 모든 테이블과 모든 필드가 나열됩니다.

누군가 도움이 될 수 있다고 생각한다면요다음은 @Daniel Verité 함수이며, 검색에 사용할 수 있는 열의 이름을 허용하는 다른 매개 변수입니다.이렇게 하면 처리 시간이 줄어듭니다.적어도 제 시험에서는 많이 줄었습니다.

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')
);

진행 상황 보고 기능을 갖춘 @Daniel Verité의 함수는 다음과 같습니다.세 가지 방식으로 진행 상황을 보고합니다.

  1. 인상 통지에 의해;
  2. 제공된 {search_seq} 시퀀스의 값을 {총 검색할 열 수}에서 0으로 내림으로써;
  3. c:\windows\domino\{domino_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;

아래 함수는 데이터베이스에 특정 문자열이 포함된 모든 테이블을 나열합니다.

 select TablesCount(‘StringToSearch’);

--데이터베이스의 모든 테이블을 반복합니다.

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;

--각 테이블의 필드를 가져옵니다.테이블의 모든 열을 사용하여 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

언급URL : https://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql

반응형

'programing' 카테고리의 다른 글

프로그래머는 SSIS를 사용해야 하며, 그렇다면 그 이유는 무엇입니까?  (0) 2023.05.08
WCF - 메시지 크기 할당량을 늘리는 방법  (0) 2023.05.08
PostgreSQL의 함수 내부에서 SELECT 결과를 반환하는 방법은 무엇입니까?  (0) 2023.05.08
WPF 데이터 그리드가 선택한 행 스타일  (0) 2023.05.08
"애저웹 앱"과 "애저앱 서비스"의 차이점은 무엇입니까?  (0) 2023.05.08

'programing'의 다른글

  • 현재글모든 테이블(PostgreSQL)에서 특정 값을 검색하는 방법은 무엇입니까?

관련글

댓글

티스토리툴바