CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
RETURNS text AS
$BODY$
DECLARE
v_table_ddl text;
column_record record;
BEGIN
FOR column_record IN
SELECT b.nspname as schema_name,
b.relname as table_name,
a.attname as column_name,
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 c.relname ~ ('^(' || p_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 || ' (';
ELSE
v_table_ddl := v_table_ddl || ',';
END IF;
IF column_record.attnum <= column_record.max_attnum THEN
v_table_ddl := v_table_ddl || chr(10) ||
' ' || column_record.column_name || ' ' || column_record.column_type || ' ' ||
column_record.column_default_value || ' ' || column_record.column_not_null;
END IF;
END LOOP;
v_table_ddl := v_table_ddl || ');';
RETURN v_table_ddl;
END;
$BODY$ LANGUAGE plpgsql;
--测试
select generate_create_table_statement('t_sys_user') as sql;
greenplum获取建表语句
于 2023-06-25 14:00:55 首次发布
该文章介绍了一个PostgreSQL的函数,用于根据给定的表名动态生成CREATETABLE语句。函数遍历指定表的列信息,包括列名、数据类型、默认值和非空约束,并构造相应的DDL语句。最后,文章展示了如何测试这个函数。
摘要由CSDN通过智能技术生成