호출 예시
SELECT * FROM GENERATE_CREATE_TABLE('테이블명');
SELECT * FROM GENERATE_CREATE_TABLE('스키마명.테이블명');
정의
-- ---------------------------------------------------------------------------------------------
-- Create Table 문 생성
-- ---------------------------------------------------------------------------------------------
-- 예시
-- - SELECT * FROM GENERATE_CREATE_TABLE('테이블명'); -- public 스키마: 스키마 이름 생략
-- - SELECT * FROM GENERATE_CREATE_TABLE('스키마명.테이블명');
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS GENERATE_CREATE_TABLE;
CREATE FUNCTION GENERATE_CREATE_TABLE(p_full_table_name varchar)
RETURNS text AS
$BODY$
DECLARE
v_table_ddl text;
column_record record;
constraint_record record;
v_schema_name varchar;
v_table_name varchar;
v_search_path varchar;
max_column_len int;
prev_statement text = '';
tmp_str text = '';
table_comment text = '';
column_comments text = '';
column_name_ text;
column_comment_ text;
column_len_ int;
BEGIN
-- 스키마와 테이블 이름 분리
IF strpos(p_full_table_name, '.') > 0 THEN
v_schema_name := split_part(p_full_table_name, '.', 1);
v_table_name := split_part(p_full_table_name, '.', 2);
ELSE
v_schema_name := 'public';
v_table_name := p_full_table_name;
END IF;
-- 해당 테이블의 컬럼 최대 길이 조회
SELECT MAX(LENGTH(attname))
FROM pg_catalog.pg_attribute
WHERE attrelid = (v_schema_name || '.' || v_table_name)::regclass
AND attnum > 0
AND NOT attisdropped
INTO max_column_len;
EXECUTE('show search_path') INTO v_search_path;
EXECUTE FORMAT('set search_path=%s', v_schema_name);
-- Loop: 컬럼 정의
FOR column_record IN
SELECT
b.nspname as schema_name,
b.relname as table_name,
a.attname as column_name,
LENGTH(a.attname) as column_len,
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
CASE WHEN
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
ELSE ''
END as column_default_value,
CASE WHEN a.attnotnull = true THEN
'NOT NULL'
ELSE
'NULL'
END as column_not_null,
a.attnum as attnum,
e.max_attnum as max_attnum
FROM
pg_catalog.pg_attribute a
INNER JOIN
(SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = v_schema_name
AND c.relname = v_table_name
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3) b
ON a.attrelid = b.oid
INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) as max_attnum
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
GROUP BY a.attrelid) e
ON a.attrelid=e.attrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_record.attnum = 1 THEN
v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
END IF;
IF column_record.attnum <= column_record.max_attnum THEN
column_record.column_type := REGEXP_REPLACE(column_record.column_type, 'character varying', 'varchar');
column_record.column_type := REGEXP_REPLACE(column_record.column_type, 'character\\(', 'char(');
column_record.column_type := REGEXP_REPLACE(column_record.column_type, ' with time zone', 'tz');
column_record.column_type := REGEXP_REPLACE(column_record.column_type, ' without time zone', '');
column_record.column_default_value := REGEXP_REPLACE(column_record.column_default_value, 'character varying', 'varchar');
-- DEFAULT NULL 제약은 제거
IF STRPOS(column_record.column_default_value, 'DEFAULT NULL') > 0 THEN
column_record.column_default_value := '';
END IF;
IF column_record.attnum = 1 THEN
tmp_str := ' ';
ELSE
tmp_str := ',';
END IF;
v_table_ddl := v_table_ddl||chr(10)||
' ' || tmp_str || column_record.column_name
|| REPEAT(' ', max_column_len - column_record.column_len)
|| ' ' || column_record.column_type
|| REPEAT(' ', 16 - LENGTH(column_record.column_type ))
|| ' ' || column_record.column_not_null
|| REPEAT(' ', 8 - LENGTH(column_record.column_not_null))
|| ' ' || column_record.column_default_value
;
-- SERIAL 컬럼이 있을 경우, 사전 생성 구문 추가
IF STRPOS(column_record.column_default_value, 'nextval') > 0 THEN
tmp_str := (REGEXP_MATCH(
column_record.column_default_value,
'''(.+)'''
))[1];
prev_statement := prev_statement || 'CREATE SEQUENCE IF NOT EXISTS ' || v_schema_name || '.' || tmp_str || ';' || chr(10);
prev_statement := prev_statement || 'ALTER SEQUENCE ' || v_schema_name || '.' || tmp_str || ' RESTART WITH 1;' || chr(10);
END IF;
END IF;
END LOOP;
v_table_ddl:=v_table_ddl||chr(10)||');';
-- 사전 생성 구문 적용
v_table_ddl := prev_statement || v_table_ddl;
-- Loop: 컬럼 제약 조건
FOR constraint_record IN
SELECT
conname AS constraint_name,
pg_get_constraintdef(c.oid) AS constraint_definition
FROM
pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
JOIN pg_class r ON r.oid = c.conrelid
WHERE
n.nspname = v_schema_name
AND r.relname = v_table_name
LOOP
v_table_ddl := v_table_ddl || chr(10) ||
'ALTER TABLE ' || v_schema_name || '.' || v_table_name ||
' ADD CONSTRAINT ' || constraint_record.constraint_name || ' ' ||
constraint_record.constraint_definition || ';';
END LOOP;
-- 테이블 코멘트 조회
SELECT d.description AS table_comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE n.nspname = v_schema_name
AND c.relname = v_table_name
INTO table_comment;
IF table_comment IS NOT NULL THEN
table_comment := chr(10) || chr(10) || 'COMMENT ON TABLE ' || v_schema_name || '.' || v_table_name || ' is ''' || table_comment || ''';';
v_table_ddl := v_table_ddl || table_comment;
END IF;
-- Loop: 컬럼 코멘트
FOR column_name_, column_comment_, column_len_ IN
SELECT a.attname AS column_name_,
pg_catalog.col_description(a.attrelid, a.attnum) AS column_comment_,
LENGTH(a.attname) AS column_len_
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (v_schema_name || '.' || v_table_name)::regclass
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_comment_ IS NOT NULL THEN
column_comments := column_comments || 'COMMENT ON COLUMN '
|| v_schema_name || '.' || v_table_name || '.' || column_name_
|| REPEAT(' ', max_column_len - column_len_) || ' is ''' || column_comment_ || ''';' || chr(10);
END IF;
END LOOP;
IF LENGTH(column_comments) > 2 THEN
v_table_ddl := v_table_ddl || chr(10) || chr(10) || column_comments;
END IF;
-- END
EXECUTE FORMAT('set search_path=%s', v_search_path);
RAISE NOTICE '%', chr(10) || v_table_ddl;
RETURN v_table_ddl;
END; $BODY$
LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;