[PostgreSQL] 이관 데이터 검증을 위한 함수

ASIS DB에서 TOBE DB로 마이그레이션을 진행한 후 이관 검증을 할 수 있는 방법은 여러가지 있을 수 있습니다. 아래와 같이 검증 시나리오를 세웠으며 기록용으로 남깁니다. TOBE가 PostgreSQL 이므로 PostgreSQL 함수를 기록합니다.

create or replace function get_data_verify(tableschema text, tables text)
  returns text as $$
  declare
  statement text default '';
  cnt number := 1;
  rec_film   record;

  cur_films cursor
    for select table_name, column_name, data_type, udt_name
    from information_schema.columns
    where table_name = tables
    order by table_name, ordinal_position;
    begin
    -- open the cursor
    open cur_films;

    loop
    -- fetch row into the film
    fetch cur_films into rec_film;
	
    -- exit when no more row to fetch
    exit when not found; 

    -- build the output
	cnt := cnt + 1;
	
    if rec_film.data_type like '%char%' then
       statement := statement||','||'COALESCE(sum(length('||rec_film.column_name||')),0) as '|| rec_film.column_name;
    end if;

    if rec_film.data_type like '%DEFINED%' then
	   if rec_film.udt_name like '%varchar2' then
          statement := statement||','||'COALESCE(sum(length('||rec_film.column_name||')),0) as '|| rec_film.column_name;
	   elsif rec_film.udt_name = 'number' then
          statement := statement||','||'COALESCE(sum('||rec_film.column_name||'),0) as '|| rec_film.column_name;
	   elsif rec_film.udt_name = 'date' then
          statement := statement||','||'COALESCE(sum((to_number(to_char('||rec_film.column_name||', ''YYYYMMDD'')))),0) as '|| rec_film.column_name;
	   end if;
    end if;

	if rec_film.data_type = 'numeric' then
          statement := statement||','||'COALESCE(sum('||rec_film.column_name||'),0) as '|| rec_film.column_name;
	end if;
	
	if rec_film.data_type = 'integer' then
          statement := statement||','||'COALESCE(sum('||rec_film.column_name||'),0) as '|| rec_film.column_name;
	end if;
	
    if rec_film.data_type like '%timestamp%' then
       statement := statement||','||'COALESCE(sum((to_number(to_char('||rec_film.column_name||', ''YYYYMMDD'')))),0) as '|| rec_film.column_name;
    end if;

    end loop;

    -- close the cursor
    close cur_films;

	if cnt < 25 then
	   for v_cnt in cnt .. 25 
	   loop
	   statement := statement||', 99999999 as col_'||v_cnt;
	   end loop;
	end if;
	
    statement := substr(statement,2);

    return 'union all select '''||tables||''' as table_name,'||statement ||' from '||tableschema||'.'||tables;
end; $$
language plpgsql;