호출 예시

CALL LINE_UP_SERIAL_COLUMN('테이블명', '컬럼명')

CALL LINE_UP_SERIAL_COLUMN('스키마명.테이블명', '컬럼명')

정의

--------------------------------------------------------------------------------------------
-- Serial 컬럼 빈 값 채워서 줄세우기
--------------------------------------------------------------------------------------------
-- [예제]
-- CALL LINE_UP_SERIAL_COLUMN('스키마명.테이블명', '컬럼명');
-- CALL LINE_UP_SERIAL_COLUMN('테이블명', '컬럼명'); -- public 테이블
--------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS LINE_UP_SERIAL_COLUMN;
        CREATE PROCEDURE LINE_UP_SERIAL_COLUMN
(
    _schema_table_name text,
    _column_only_name text
)
AS $$
DECLARE 
    _dot_pos int;
    _sequence_name text;
    _schema_only_name text;
    _table_only_name text;
    _next_seq_value int;
BEGIN
    _dot_pos := strpos(_schema_table_name, '.');
    
    -- 스키마명, 테이블명
    IF _dot_pos = 0 THEN
        _schema_only_name := 'public';
        _table_only_name := _schema_table_name;
        _schema_table_name := _schema_only_name || '.' || _table_only_name;
    ELSE
        _schema_only_name := substring(_schema_table_name, 0, _dot_pos);
        _table_only_name := substring(_schema_table_name, _dot_pos + 1);
    END IF;
    
    -- 시퀀스명
    SELECT (REGEXP_MATCH(column_default, '''(.+)'''))[1] AS seq
    FROM information_schema.columns
    WHERE table_schema = _schema_only_name
      AND table_name = _table_only_name
      AND column_name = _column_only_name
      AND column_default LIKE 'nextval(%'
    INTO _sequence_name;
    
    -- [1] Serial 컬럼 MAX() 값 이후로 줄세우기 (PK값 중복 방지)
    EXECUTE FORMAT('
WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY %2$s ASC) + MAX(%2$s) OVER () AS next_serial, ctid 
    FROM %1$s
)
UPDATE %1$s
   SET %2$s = cte.next_serial
  FROM cte
 WHERE %1$s.ctid = cte.ctid;
   ', _schema_table_name, _column_only_name);
    
    -- [2] Serial 컬럼 제대로 줄세우기
    EXECUTE FORMAT('
WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY %2$s ASC) AS next_serial, ctid 
    FROM %1$s
)
UPDATE %1$s
   SET %2$s = cte.next_serial
  FROM cte
 WHERE %1$s.ctid = cte.ctid;
   ', _schema_table_name, _column_only_name);
   
   -- 다음 Sequence 값 생성
   EXECUTE 'SELECT MAX('||_column_only_name||') FROM '||_schema_table_name INTO _next_seq_value;
   
   -- Sequence 값 설정
   EXECUTE 'SELECT setval(' || quote_literal(_sequence_name) || ', ' || _next_seq_value || ', true)';
   -- EXECUTE FORMAT('ALTER SEQUENCE %s RESTART WITH %s', _sequence_name, _next_seq_value);
   
   RAISE NOTICE 'TABLE : [%]', _schema_table_name;
   RAISE NOTICE 'COLUMN: [%]', _column_only_name;
   RAISE NOTICE 'MAX VALUE: [%]', _next_seq_value;
    
END; $$
LANGUAGE plpgsql;