· 7 years ago · Feb 01, 2019, 05:02 PM
1-- SELECT app_table_sql('orders', TRUE, FALSE, ARRAY['bill_last_name', 'bill_first_name']);
2-- Adapted from: https://stackoverflow.com/a/16154183/1148902
3CREATE OR REPLACE FUNCTION app_table_sql(
4 p_table varchar,
5 p_replace boolean DEFAULT TRUE,
6 p_explicit boolean DEFAULT FALSE,
7 p_sort varchar[] DEFAULT ARRAY[]::varchar[]
8) RETURNS text AS
9$DDL$
10DECLARE
11 v_val int;
12 v_key int;
13 v_ddl text DEFAULT '';
14 v_sql varchar DEFAULT '';
15 v_sequences varchar[];
16 v_sequence record;
17 v_column record;
18 v_constraint record;
19 v_schema record;
20BEGIN
21 IF (SELECT to_regclass(p_table::cstring) IS NULL) THEN
22 RAISE EXCEPTION 'Table not found --> %', p_table USING HINT
23 = 'Please verify that the table exists and is accessible to the current user.';
24 END IF;
25
26 CREATE OR REPLACE FUNCTION pg_temp.app_idx(anyarray, anyelement)
27 RETURNS int AS
28 $IDX$
29 SELECT i FROM (
30 SELECT generate_series(array_lower($1, 1), array_upper($1, 1))
31 ) AS g(i)
32 WHERE $1[i] = $2
33 LIMIT 1;
34 $IDX$ LANGUAGE SQL IMMUTABLE;
35
36 FOR v_sequence IN
37 SELECT
38 s.sequence_name,
39 c.sequence_short,
40 c.sequence_long,
41 s.start_value,
42 s.minimum_value,
43 s.maximum_value,
44 s.increment,
45 c.table_catalog,
46 c.table_name,
47 c.column_name
48 FROM information_schema.sequences AS s
49 INNER JOIN (
50 SELECT c.*,
51 split_part(pg_get_serial_sequence(c.table_name, c.column_name), '.', 2) AS sequence_short,
52 pg_get_serial_sequence(c.table_name, c.column_name) AS sequence_long
53 FROM information_schema.columns AS c
54 WHERE c.column_default LIKE 'nextval(%'
55 AND c.table_name ~ ('^(' || p_table || ')$')
56 AND c.table_schema ~ ('^(' || current_schema() || ')$')
57 ) AS c ON (
58 (s.sequence_name = c.sequence_short AND s.sequence_schema = c.table_schema) OR
59 (CONCAT(s.sequence_schema, '.', s.sequence_name) = c.sequence_long)
60 )
61 LOOP
62 IF p_replace = TRUE THEN
63 v_ddl := v_ddl || chr(10) || chr(10)
64 || 'DO $$' || chr(10)
65 || 'BEGIN' || chr(10)
66 || ' IF (SELECT to_regclass(' || v_sequence.sequence_short || ') IS NOT NULL) THEN' || chr(10)
67 || ' DROP SEQUENCE ' || v_sequence.sequence_short || ' CASCADE;' || chr(10)
68 || ' END IF;' || chr(10)
69 || 'END$$;' || chr(10) || chr(10);
70 END IF;
71
72 v_ddl := v_ddl
73 || 'CREATE SEQUENCE ' || v_sequence.sequence_long || chr(10)
74 || ' START WITH ' || v_sequence.start_value || chr(10)
75 || ' INCREMENT BY ' || v_sequence.increment || chr(10);
76
77 IF v_sequence.minimum_value ~ '^[0-9-]+$' THEN
78 v_ddl := v_ddl || ' MINVALUE ' || v_sequence.minimum_value || chr(10);
79 ELSE
80 v_ddl := v_ddl || ' NO MINVALUE' || chr(10);
81 END IF;
82
83 IF v_sequence.maximum_value ~ '^[0-9-]+$' THEN
84 v_ddl := v_ddl || ' MAXVALUE ' || v_sequence.maximum_value;
85 ELSE
86 v_ddl := v_ddl || ' NO MAXVALUE';
87 END IF;
88
89 EXECUTE format('SELECT cache_value FROM %s', v_sequence.sequence_name) INTO v_val;
90
91 v_ddl := v_ddl || chr(10) || ' CACHE ' || v_val;
92
93 v_ddl := v_ddl || ';' || chr(10) || chr(10);
94
95 v_sequences = array_append(v_sequences, CONCAT(
96 'ALTER SEQUENCE ', v_sequence.sequence_long, ' ',
97 'OWNED BY ', v_sequence.table_name, '.',
98 v_sequence.column_name, ';'
99 )::varchar);
100 END LOOP;
101
102 FOR v_column IN
103 SELECT
104 b.nspname AS schema_name,
105 b.relname AS table_name,
106 a.attname AS column_name,
107 a.attnum AS attnum,
108 e.max_attnum AS max_attnum,
109 pg_catalog.format_type(a.atttypid, a.atttypmod) AS column_type,
110 CASE WHEN
111 (
112 SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128)
113 FROM pg_catalog.pg_attrdef AS d
114 WHERE d.adrelid = a.attrelid
115 AND d.adnum = a.attnum
116 AND a.atthasdef
117 ) IS NOT NULL THEN 'DEFAULT ' || (
118 SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128)
119 FROM pg_catalog.pg_attrdef AS d
120 WHERE d.adrelid = a.attrelid
121 AND d.adnum = a.attnum
122 AND a.atthasdef
123 )
124 ELSE
125 ''
126 END AS default_value, (
127 SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 8)
128 FROM pg_catalog.pg_attrdef AS d
129 WHERE d.adrelid = a.attrelid
130 AND d.adnum = a.attnum
131 AND a.atthasdef
132 ) AS default_basic,
133 CASE WHEN a.attnotnull = true THEN
134 'NOT NULL'
135 ELSE
136 'NULL'
137 END AS nullable
138 FROM
139 pg_catalog.pg_attribute AS a
140 INNER JOIN (
141 SELECT c.oid, n.nspname, c.relname
142 FROM pg_catalog.pg_class AS c
143 LEFT JOIN pg_catalog.pg_namespace AS n ON (n.oid = c.relnamespace)
144 WHERE c.relname ~ ('^(' || p_table || ')$')
145 AND pg_catalog.pg_table_is_visible(c.oid)
146 ORDER BY 2, 3
147 ) AS b ON (a.attrelid = b.oid)
148 INNER JOIN (
149 SELECT a.attrelid, max(a.attnum) AS max_attnum
150 FROM pg_catalog.pg_attribute AS a
151 WHERE a.attnum > 0
152 AND NOT a.attisdropped
153 GROUP BY a.attrelid
154 ) AS e ON (a.attrelid = e.attrelid)
155 WHERE a.attnum > 0
156 AND NOT a.attisdropped
157 ORDER BY
158 CASE WHEN a.attnum = 1 THEN 1 ELSE 2 END,
159 pg_temp.app_idx(p_sort::varchar[], a.attname::varchar)
160 LOOP
161 IF v_column.attnum = 1 THEN
162 v_ddl := v_ddl
163 || 'CREATE TABLE '
164 || v_column.schema_name || '.'
165 || v_column.table_name || ' (';
166 ELSE
167 v_ddl := v_ddl || ',';
168 END IF;
169
170 v_ddl := v_ddl
171 || chr(10) || ' '
172 || v_column.column_name || ' '
173 || v_column.column_type || ' '
174 || v_column.default_value || ' '
175 || v_column.nullable;
176 END LOOP;
177
178 FOR v_constraint IN
179 SELECT
180 pg_get_constraintdef(c.oid) AS constraint_rule,
181 lead(c.oid) OVER() IS NULL AS is_last_row
182 FROM pg_constraint AS c
183 JOIN pg_namespace AS n ON (n.oid = c.connamespace)
184 AND c.conrelid::regclass::text ~ ('^(' || p_table || ')$')
185 LOOP
186 IF v_constraint.is_last_row THEN
187 v_ddl := v_ddl || ',' || chr(10) || ' ' || v_constraint.constraint_rule;
188 ELSE
189 v_ddl := v_ddl || ',' || chr(10) || ' ' || v_constraint.constraint_rule;
190 END IF;
191 END LOOP;
192
193 v_ddl := v_ddl || chr(10) || ');';
194
195 IF array_length(v_sequences, 1) > 0 THEN
196 FOREACH v_sql IN ARRAY v_sequences LOOP
197 v_ddl := v_ddl || chr(10) || chr(10) || v_sql;
198 END LOOP;
199 END IF;
200
201 RETURN v_ddl;
202END;
203$DDL$ LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;