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);
63 v_schema := column_record.schema_name;
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 || ';';
79 WHEN i.indisprimary THEN 'ALTER TABLE '||v_schema||'.'||p_table_name||' ADD CONSTRAINT ' || c.relname || ' PRIMARY KEY (' || c.attname || ');'
81 WHEN i.indisunique THEN 'CREATE UNIQUE INDEX '|| c.relname || ' ON ' ||v_schema||'.'||p_table_name||' USING btree (' || c.attname || );'
83 'CREATE INDEX '|| c.relname || ' ON ' ||v_schema||'.'||p_table_name||' USING btree (' || c.attname || );'
85 FROM pg_index AS i, pg_class AS c, pg_attribute AS a
86 WHERE i.indexrelid = c.oid AND i.indexrelid = a.attrelid AND i.indrelid = (v_schema|| '.' || p_table_name)::regclass
90 v_table_ddl:= v_table_ddl||chr(10)||
99 --v_table_ddl:=v_table_ddl||');';
103 LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
105 SELECT schema_create_table_statement('item');