[PostgreSQL] 함수(function)나 프로시저(procedure) 내용을 검색하거나 조회하는 방법 : 한방쿼리

PostgreSQL에서 함수(function)나 프로시저(procedure)의 소스 내용에 특정 텍스트를 검색하려면, 함수 본문을 저장하는 시스템 카탈로그인 **pg_proc**의 prosrc 컬럼을 활용하면 됩니다. 아래에 이를 수행하는 구체적인 방법을 정리하였습니다.

---

1. pg_proc에서 소스 내용 검색

pg_proc.prosrc 컬럼은 함수 또는 프로시저의 본문(소스 코드)을 포함합니다. 특정 키워드를 검색하려면 다음과 같은 SQL 쿼리를 사용할 수 있습니다.

특정 키워드 검색

SELECT nspname AS schema_name,
       proname AS function_name,
       prosrc AS function_body
FROM pg_proc
JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
WHERE prosrc LIKE '%검색할_키워드%';

 

• nspname: 함수가 속한 스키마 이름
• proname: 함수 이름
• prosrc: 함수의 소스 코드
• LIKE '%검색할_키워드%': 특정 키워드 검색

---

2. INFORMATION_SCHEMA.ROUTINES에서 검색

INFORMATION_SCHEMA.ROUTINES 뷰에서도 소스 코드(routine_definition)를 검색할 수 있습니다.

예제: 특정 키워드 검색

SELECT specific_schema AS schema_name,
       routine_name AS function_name,
       routine_definition AS function_body
FROM information_schema.routines
WHERE routine_definition LIKE '%검색할_키워드%'
  AND routine_type = 'FUNCTION'; -- 프로시저 검색 시 'PROCEDURE'로 변경

 

---

3. 함수와 프로시저를 포함한 전체 검색

함수와 프로시저를 모두 포함하여 검색하려면 pg_proc를 사용하는 것이 편리합니다.

SELECT nspname AS schema_name,
       proname AS object_name,
       prosrc AS object_body,
       CASE
           WHEN proisagg THEN 'AGGREGATE'
           WHEN proiswindow THEN 'WINDOW FUNCTION'
           ELSE 'FUNCTION/PROCEDURE'
       END AS object_type
FROM pg_proc
JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
WHERE prosrc LIKE '%검색할_키워드%';

 

---

4. pgAdmin에서 소스 검색

pgAdmin에서 GUI를 사용하여 소스 코드를 검색하려면:

1. Functions 또는 Procedures 목록으로 이동합니다.
2. 각 함수를 열어 Definition 탭에서 소스 코드 내용을 확인합니다.

---

5. SQL 스크립트 활용: 정교한 검색

아래는 키워드와 스키마 필터를 동시에 사용할 수 있는 SQL 스크립트 예제입니다.

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT nspname AS schema_name,
               proname AS function_name,
               prosrc AS function_body
        FROM pg_proc
        JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
        WHERE prosrc LIKE '%검색할_키워드%'
    LOOP
        RAISE NOTICE 'Schema: %, Function: %, Code: %', r.schema_name, r.function_name, r.function_body;
    END LOOP;
END;
$$;

 

---

주의사항

1. 보안 함수: SECURITY DEFINER 또는 C 언어로 작성된 함수는 prosrc에 소스가 저장되지 않을 수 있습니다.
2. 대소문자 구분: PostgreSQL에서 LIKE는 기본적으로 대소문자를 구분합니다. 대소문자를 구분하지 않는 검색을 하려면 ILIKE를 사용하세요.

WHERE prosrc ILIKE '%검색할_키워드%'

 

---

요약

• pg_proc.prosrc 또는 information_schema.routines에서 LIKE 또는 ILIKE를 사용하여 키워드를 검색.
• GUI 도구(pgAdmin)를 사용하면 소스 검색이 쉽지만, 대량 검색은 SQL이 유용.
• 복잡한 검색이나 결과 필터링은 스크립트를 활용.