1 CREATE OR REPLACE FUNCTION schema_create_table_statement(p_table_name varchar)
11 b.nspname as schema_name,
12 b.relname as table_name,
13 a.attname as column_name,
14 pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
16 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
17 FROM pg_catalog.pg_attrdef d
18 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
19 'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
20 FROM pg_catalog.pg_attrdef d
21 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
24 END as column_default_value,
25 CASE WHEN a.attnotnull = true THEN
29 END as column_not_null,
31 e.max_attnum as max_attnum
33 pg_catalog.pg_attribute a
38 FROM pg_catalog.pg_class c
39 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
40 WHERE c.relname ~ ('^('||p_table_name||')$')
41 AND pg_catalog.pg_table_is_visible(c.oid)
47 max(a.attnum) as max_attnum
48 FROM pg_catalog.pg_attribute a
50 AND NOT a.attisdropped
51 GROUP BY a.attrelid) e
52 ON a.attrelid=e.attrelid
54 AND NOT a.attisdropped
57 IF column_record.attnum = 1 THEN
58 v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' ();'||chr(10)||chr(10);
65 IF column_record.attnum <= column_record.max_attnum THEN
66 v_table_ddl:= v_table_ddl||chr(10)||
67 'ALTER TABLE '||column_record.schema_name||'.'||column_record.table_name||
68 ' ADD COLUMN ' ||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null || ';';
72 --v_table_ddl:=v_table_ddl||');';
76 LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
78 SELECT schema_create_table_statement('item');