호출 예시

SELECT * FROM F_GATHER_ALL_FK_REF_TO_TABLE('테이블명');

SELECT * FROM F_GATHER_ALL_FK_REF_TO_TABLE('스키마명.테이블명');

정의

-- ---------------------------------------------------------------------------------------------
-- 지정 테이블을 FK로 참조하는 모든 컬럼의 FK 일괄 제거/재생성 쿼리 생성
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - SELECT * FROM F_GATHER_ALL_FK_REF_TO_TABLE('테이블명'); -- public 스키마: 스키마 이름 생략
--   - SELECT * FROM F_GATHER_ALL_FK_REF_TO_TABLE('스키마명.테이블명');
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS public.F_GATHER_ALL_FK_REF_TO_TABLE;
CREATE FUNCTION public.F_GATHER_ALL_FK_REF_TO_TABLE
(
    t_name TEXT
)
RETURNS TABLE (
    query_txt TEXT
) AS $$
DECLARE
    _schema_name TEXT;
    _table_name  TEXT;
    _schema_table_name TEXT;
    _search_path TEXT;
BEGIN
    
    IF strpos(t_name, '.') > 0 THEN
        _schema_name := split_part(t_name, '.', 1);
        _table_name  := split_part(t_name, '.', 2);
        _schema_table_name := t_name;
    ELSE
        _schema_name := 'public';
        _table_name  := t_name;
        _schema_table_name := t_name;
    END IF;
    
    EXECUTE('show search_path') INTO _search_path;
    EXECUTE('set search_path=_');

    RETURN QUERY
WITH 
CTE1 AS
(
     SELECT
            connamespace::regnamespace::TEXT AS SCHEMA_NAME,
            CASE WHEN LENGTH(split_part(conrelid::regclass::TEXT, '.', 2)) > 0
                 THEN split_part(conrelid::regclass::TEXT, '.', 2) 
                 ELSE conrelid::regclass::TEXT END 
              AS TABLE_NAME,
            conname                          AS FK_NAME,
            pg_get_constraintdef(oid)        AS FK_DEF
       FROM pg_constraint
      WHERE contype = 'f'
),
CTE2 AS
(
     SELECT *
       FROM CTE1
      WHERE FK_DEF ILIKE '%references%' || _schema_table_name || '(%'
   ORDER BY 1, 2
)
 SELECT STRING_AGG('ALTER TABLE ' || SCHEMA_NAME || '.' || TABLE_NAME || ' DROP CONSTRAINT IF EXISTS ' || FK_NAME || ';', E'\\n')
   FROM CTE2
 UNION
 SELECT STRING_AGG('ALTER TABLE ' || SCHEMA_NAME || '.' || TABLE_NAME || ' ADD CONSTRAINT ' || FK_NAME || ' ' || FK_DEF || ';', E'\\n')
   FROM CTE2
 ORDER BY 1 DESC
;
    EXECUTE FORMAT('set search_path=%s', _search_path);
END; $$
LANGUAGE plpgsql;