pl/pgsql 활용하여 전체 DB에 있는 Data 조회 쿼리

postgresql 에서 전체 DB에 있는 모든 테이블 및 컬럼에서 특정 값을 포함하는 Data 조회하는 쿼리 작성하였다.

 

처음 접해보는 개념이라 좀 빡세긴 했는데 역시 Gpt를 활용하여 시간을 단축하였다.

 

새별이라 피곤하니 설명은 생략./

create or replace function searchColumData(n text) -- 없으면 선언 있으면 재 선언
returns void as $$ -- 리턴 타입 void 및 $$는 $$안에 있는걸 다 함수로 취급
declare -- 변수 선언
	test RECORD; -- 레코드 타입
	test2 RECORD;
	table_name_co text;
	dynamic_query text;
begin -- 함수 실행
	for test in select * from information_schema.columns where table_catalog = 'ecvam' and table_schema = 'public' and data_type ='character varying' loop -- 반복문 실행
		if(test.table_name not like '%20%') then -- 조건문 실행 (postgre의 경우 숫자가 테이블 앞에 오는 경우는 조금 다르게 호출해야 해서 조건문 사용)
			table_name_co := CONCAT(test.table_schema,'.',test.table_name); -- 테이블 명 선언
			dynamic_query := format('SELECT %I FROM %I where %I like ', test.column_name ,table_name_co,test.column_name); -- 테이블 명과 조회된 컬럼 명으로 새로운 쿼리 만듬
			dynamic_query := replace(dynamic_query,'"',''); -- 이건 콤마 문제 때문에 안돌아가서 넣음
			dynamic_query := concat(dynamic_query,quote_literal(n)); -- 마찬가지
		else -- 이하 생략
			table_name_co := test.table_name;
			dynamic_query := format('SELECT %I FROM %I where %I like ', test.column_name ,table_name_co,test.column_name);
			dynamic_query := concat(dynamic_query,quote_literal(n));
		end if;
		--RAISE NOTICE '%', dynamic_query; 테스트 용
		for test2 in EXECUTE dynamic_query using n loop -- 새로 만든 쿼리 반복문
			RAISE NOTICE '테이블명: %, 컬럼명: %, 데이터명: %',table_name_co,test.column_name, test2; -- 리턴 타입입 void이기 때문에 메세지로 대체 콘솔이라고 생각하3
		end loop;
	end loop;
end;
$$ language plpgsql; -- 언어 선언

DROP FUNCTION IF EXISTS searchColumData(n text); -- 함수 만든거 지워주3

select searchColumData('%김%'); -- 꼭 변수에 like 조건절 기준으로 넣어야 함.