호출 예시

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

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

정의

-- ---------------------------------------------------------------------------------------------
-- 테이블 내 모든 FK 일괄 제거/재생성 쿼리 생성
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - SELECT * FROM F_GATHER_ALL_FK_IN_TABLE('테이블명'); -- public 스키마: 스키마 이름 생략
--   - SELECT * FROM F_GATHER_ALL_FK_IN_TABLE('스키마명.테이블명');
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS F_GATHER_ALL_FK_IN_TABLE;
CREATE FUNCTION F_GATHER_ALL_FK_IN_TABLE
(
    t_name TEXT
)
RETURNS TABLE (
    query_txt TEXT
) AS $$
DECLARE
    _schema_name TEXT;
    _table_name  TEXT;
    _schema_table_name 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;

    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 SCHEMA_NAME = _schema_name
        AND TABLE_NAME = _table_name
--         AND pg_get_constraintdef(oid) NOT SIMILAR TO '%REFERENCES ' || conrelid::regclass::TEXT || '%' -- 자기 테이블 FK 참조
   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
;
END; $$
LANGUAGE plpgsql;