1 CREATE OR REPLACE FUNCTION schema_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||' ();'||chr(10)||chr(10);
63 IF column_record.attnum <= column_record.max_attnum THEN
64 v_table_ddl:= v_table_ddl||chr(10)||
65 'ALTER TABLE '||column_record.schema_name||'.'||column_record.table_name||
66 ' ADD COLUMN ' ||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null || ";";
70 --v_table_ddl:=v_table_ddl||');';
74 LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
76 SELECT schema_create_table_statement('item');