[PostgreSQL] Function3

이번 장은 for문과 loop를 이용하여 function을 사용하는 법을 익혀보려고 합니다. 우선은 천천히 감을 잡아가기 위해 나름대로의 난이도를 생각하여 쉬운 난이도부터 코드를 늘려가며 난이도를 높여보려고 합니다.

for문과 loop 사용 연습

DO $$
DECLARE
    i INT;
BEGIN
    FOR i IN 1..5 LOOP
        -- 각 반복에서 실행할 코드
        RAISE NOTICE '현재 값: %', i;

        -- 필요한 경우 반복문 종료를 확인하는 조건문
        IF i = 3 THEN
            EXIT;  -- 전체 함수를 빠져나가려면 RETURN;을 사용할 수도 있습니다.
        END IF;
    END LOOP;
END $$;

이에 대한 결과로 아래와 같은 값이 콘솔에 등장합니다.

NOTICE:  현재 값: 1
NOTICE:  현재 값: 2
NOTICE:  현재 값: 3
DO

여기서 RAISE NOTICE 의 경우 python에서 print와 비슷한 역할을 수행하는 듯 보입니다. 콘솔에 로그를 남기는 역할을 합니다.

예제1
그렇다면 이번엔 i에 숫자가 아닌 row를 대입해보겠습니다.

DO $$ 
DECLARE
    db_name TEXT;
BEGIN
    FOR db_name IN (SELECT datname FROM pg_database)
    LOOP
        -- 각 반복에서 실행할 코드
        RAISE NOTICE '현재 데이터베이스: %', db_name;
        
        -- 필요한 경우 반복문 종료를 확인하는 조건문
        -- (이 경우에는 종료 조건이 없습니다)
    END LOOP;
END $$;

결과는 아래와 같습니다.

NOTICE:  현재 데이터베이스: postgres
NOTICE:  현재 데이터베이스: template1
NOTICE:  현재 데이터베이스: template0
DO

예제2
이번에는 각 DB 별 스키마를 출력하는 쿼리를 출력하는 함수 생행문을 만들어보도록 하겠습니다.

DO $$
DECLARE
    db_record RECORD;
    schema_name TEXT;
BEGIN
    FOR db_record IN (SELECT datname FROM pg_database)
    LOOP
        -- 각 반복에서 실행할 코드
        RAISE NOTICE '현재 데이터베이스: %', db_record.datname;

        FOR schema_name IN (SELECT nspname FROM pg_namespace)
        LOOP
            RAISE NOTICE '  스키마: %', schema_name;
        END LOOP;

        -- 필요한 경우 반복문 종료를 확인하는 조건문
        -- (이 경우에는 종료 조건이 없습니다)
    END LOOP;
END $$;

NOTICE:  현재 데이터베이스: postgres
NOTICE:    스키마: pg_toast
NOTICE:    스키마: pg_catalog
NOTICE:    스키마: public
NOTICE:    스키마: information_schema
NOTICE:    스키마: hint_plan
NOTICE:  현재 데이터베이스: template1
NOTICE:    스키마: pg_toast
NOTICE:    스키마: pg_catalog
NOTICE:    스키마: public
NOTICE:    스키마: information_schema
NOTICE:    스키마: hint_plan
NOTICE:  현재 데이터베이스: template0
NOTICE:    스키마: pg_toast
NOTICE:    스키마: pg_catalog
NOTICE:    스키마: public
NOTICE:    스키마: information_schema
NOTICE:    스키마: hint_plan
DO

결과의 모습은 잘 나왔지만 실제와는 맞지 않습니다. 모든 DB 마다 hint_plan 이라나는 스키마가 존재하지 않습니다. postgres DB에서만 생성했기 때문에 postgres DB에서만 존재해야합니다. 따라서 단순히 워딩을 받아와 이중 for문을 실행한 것으로 보입니다.

예제3

그렇다면 이번에는 실제 값을 구하기 위해서 실제 DB를 접속하는 과정까지 실행해보려고 합니다.

DO $$
DECLARE
    db_record RECORD;
    schema_name TEXT;
BEGIN
    FOR db_record IN (SELECT datname FROM pg_database)
    LOOP
        -- 각 반복에서 실행할 코드
        RAISE NOTICE '현재 데이터베이스: %', db_record.datname;

        -- 현재 데이터베이스에 연결
        PERFORM pg_terminate_backend(pg_backend_pid()); -- 현재 연결 종료

        FOR schema_name IN (SELECT nspname FROM pg_namespace )
        LOOP
            RAISE NOTICE '  스키마: %', schema_name;
        END LOOP;

        -- 데이터베이스 연결 종료
        PERFORM pg_terminate_backend(pg_backend_pid());
    END LOOP;
END $$;

결과는 아래와 같습니다.
결과는 실패입니다. 이유를 살펴보면 현재 DB에서 연결을 종료하는 부분이 있는데 이 부분이 끊어지며 자동 복구가 된 채로 함수는 종료된 듯 보입니다. 여기서는 Perform 이 함수 생성 문법에서 어떤 역할을 하는지 공부할 수 있었습니다.

NOTICE:  현재 데이터베이스: postgres
FATAL:  terminating connection due to administrator command
CONTEXT:  SQL statement "SELECT pg_terminate_backend(pg_backend_pid())"
PL/pgSQL function inline_code_block line 12 at PERFORM
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

따라서 postgresql 에서 모든 DB별 스키마 정보를 획득하는 것은 불가능한 것으로 보여집니다. 각 DB에 접속하여 함수 실행문을 통해 일일이 확인하는 수 밖에 없을 것으로 보입니다.