1 CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
10 b.nspname as schema_name,
11 b.relname as table_name,
12 a.attname as column_name,
13 pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
15 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
16 FROM pg_catalog.pg_attrdef d
17 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
18 'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
19 FROM pg_catalog.pg_attrdef d
20 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
23 END as column_default_value,
24 CASE WHEN a.attnotnull = true THEN
28 END as column_not_null,
30 e.max_attnum as max_attnum
32 pg_catalog.pg_attribute a
37 FROM pg_catalog.pg_class c
38 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
39 WHERE c.relname ~ ('^('||p_table_name||')$')
40 AND pg_catalog.pg_table_is_visible(c.oid)
46 max(a.attnum) as max_attnum
47 FROM pg_catalog.pg_attribute a
49 AND NOT a.attisdropped
50 GROUP BY a.attrelid) e
51 ON a.attrelid=e.attrelid
53 AND NOT a.attisdropped
56 IF column_record.attnum = 1 THEN
57 v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
59 v_table_ddl:=v_table_ddl||',';
62 IF column_record.attnum <= column_record.max_attnum THEN
63 v_table_ddl:=v_table_ddl||chr(10)||
64 ' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
68 v_table_ddl:=v_table_ddl||');';
72 LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;