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 || ';';
71 v_table_ddl:= v_table_ddl||chr(10);
79 WHEN i.indisprimary THEN 'ALTER TABLE '||v_schema||'.'||p_table_name||' ADD CONSTRAINT ' || c.relname || ' PRIMARY KEY (' || a.attname || ');'
81 WHEN i.indisunique THEN 'CREATE UNIQUE INDEX '|| c.relname || ' ON ' ||v_schema||'.'||p_table_name||' USING btree (' || a.attname || ');'
83 'CREATE INDEX '|| c.relname || ' ON ' ||v_schema||'.'||p_table_name||' USING btree (' || a.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)||
94 v_table_ddl:= v_table_ddl||chr(10);
99 tc.constraint_name, tc.table_name, kcu.column_name,
100 ccu.table_name AS foreign_table_name,
101 ccu.column_name AS foreign_column_name
103 information_schema.table_constraints AS tc
104 JOIN information_schema.key_column_usage AS kcu
105 ON tc.constraint_name = kcu.constraint_name
106 JOIN information_schema.constraint_column_usage AS ccu
107 ON ccu.constraint_name = tc.constraint_name
108 WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name=p_table_name
112 v_table_ddl:= v_table_ddl||chr(10)||
113 'ALTER TABLE ' ||v_schema||'.'||p_table_name||' ADD CONSTRAINT ' || column_record.constraint_name ||
114 ' FOREIGN KEY (' || column_record.column_name || ') REFERENCES ' || column_record.foreign_table_name || '(' || column_record.foreign_column_name || ') MATCH SIMPLE;';
124 v_table_ddl:= v_table_ddl||chr(10);
127 --v_table_ddl:=v_table_ddl||');';
131 LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
133 SELECT schema_create_table_statement('item');