[PostgreSQL] Function 1

PostgreSQL를 기반으로 한 이기종 데이터베이스 간 마이그레이션을 진행하는 프로젝트가 생겼습니다. 대부분 Oracle이나 tibero와 같이 시스템카탈로그 조회가 어렵지 않은 DB를 소스 DB로 다뤘었고, 이 때는 DB 현황분석하는 것이 그닥 어렵지 않았습니다.

하지만 소스 DB로 PG를 만나니 이야기가 좀 달랐습니다. 인지하고 있었지만 시스템카탈로그를 조회하는 것은 꽤나 까다롭습니다. 각 DB 별 Schema를 조회하는 것, 각 DB의 Schema별 사이즈를 조회하는 것, 각 DB의 Schema별 테이블의 사이즈를 조회하는 것을 조회하고 싶지만 여러가지 카탈로그 뷰를 확인해봤을 때 조인으로 만들 수 없어보였습니다.

따라서 Function을 이용하여 이와같은 결과물을 얻어보고자 합니다.
Function을 조금씩 수정해서 사용해본 적은 있지만 새롭게 만드는 경우는 드물었기 때문에 이참에 공부를 해볼까합니다.

따라서 단계별로 나눠서 쉬운 Function부터 만들고 난이도를 점점 높여 원하는 Function을 만드는 것까지 기록해볼까합니다.

이번 장에서는 db이름을 매개변수로 받아 해당 db의 모든 스키마, 테이블을 반환하는 함수를 만듭니다.

1차 시도

CREATE OR REPLACE FUNCTION get_tables_in_schema(db_name text)
RETURNS TABLE (
    schema_name text,
    table_name text
) AS
$$
BEGIN
    RETURN QUERY
    SELECT
        table_schema,
        table_name
    FROM
        information_schema.tables 
    WHERE
        table_catalog = db_name;
END;
$$ LANGUAGE 'plpgsql';

제가 처음 작성한 함수 DDL 문은 위와 같습니다.
최종 함수 DDL문까지 작성하는 동안 여러가지 에러를 마주하여 에러코드와 함께 정리합니다.

1차 에러

ERROR: column reference "table_name" is ambiguous

해당 에러는 from 절 아래 테이블에 별칭을 달아주지 않아서 생기는 에러였습니다.
따라서 별칭을 부여하고 다시 생성합니다.

2차 시도

CREATE OR REPLACE FUNCTION get_tables_in_schema(db_name text)
RETURNS TABLE (
    schema_name text,
    table_name text
) AS
$$
BEGIN
    RETURN QUERY
    SELECT
        t.table_schema,
        t.table_name
    FROM
        information_schema.tables t 
    WHERE
        t.table_catalog = db_name;
END;
$$ LANGUAGE 'plpgsql';

이번에는 아래와 같은 에러가 발생합니다.

2차 에러

ERROR: structure of query does not match function result type

해당 에러는 최종적으로 반환할 테이블과 내부에서 실행한 결과 테이블의 컬럼 데이터 타입이 일치하지 않는 것으로 추정됩니다. 따라서 동일하게 text로 타입 형변환을 시켜줍니다.

아래의 쿼리가 최종 쿼리입니다.
저는 information_schema의 table_catalog까지 text 타입으로 변환해주는 것을 놓쳐서 시간을 낭비했네요.

최종 쿼리

CREATE OR REPLACE FUNCTION get_tables_in_schema(db_name text)
RETURNS TABLE (
    schema_name text,
    table_name text
) AS
$$
BEGIN
    RETURN QUERY
    SELECT
        t.table_schema::text,
        t.table_name::text
    FROM
        information_schema.tables t 
    WHERE
        t.table_catalog::text = db_name;
END;
$$ LANGUAGE 'plpgsql';