table-definition.ts807 lines · main

1	/**
2	* Derived from https://github.com/MichaelDBA/pg_get_tabledef
3	* NOTE: when updating, \n must be replaced with \\n in the SQL below
4	* public.x is replaced with pg_temp.x
5
6	* REMOVE (as it breaks the encoding for some reason):
7	* SELECT REPLACE(REPLACE(setting, '"$user"', '$user'), '$user', '"$user"') INTO search_path_old
8	* FROM pg_settings
9	* WHERE name = 'search_path';
10	*/
11
12	import { joinSqlFragments, literal, safeSql, type SafeSqlFragment } from '../../../pg-format'
13
14	export const CREATE_PG_GET_TABLEDEF_SQL = safeSql`
15	DROP TYPE IF EXISTS pg_temp.tabledefs CASCADE;
16	CREATE TYPE pg_temp.tabledefs AS ENUM ('PKEY_INTERNAL','PKEY_EXTERNAL','FKEYS_INTERNAL', 'FKEYS_EXTERNAL', 'COMMENTS', 'FKEYS_NONE', 'INCLUDE_TRIGGERS', 'NO_TRIGGERS');
17
18	-- SELECT * FROM pg_temp.pg_get_coldef('sample','orders','id');
19	-- DROP FUNCTION pg_temp.pg_get_coldef(text,text,text,boolean);
20	CREATE OR REPLACE FUNCTION pg_temp.pg_get_coldef(
21	in_schema text,
22	in_table text,
23	in_column text,
24	oldway boolean default False
25	)
26	RETURNS text
27	LANGUAGE plpgsql VOLATILE
28	AS
29	$$
30	DECLARE
31	v_coldef text;
32	v_dt1 text;
33	v_dt2 text;
34	v_dt3 text;
35	v_nullable boolean;
36	v_position int;
37	v_identity text;
38	v_generated text;
39	v_hasdflt boolean;
40	v_dfltexpr text;
41
42	BEGIN
43	IF oldway THEN
44	SELECT pg_catalog.format_type(a.atttypid, a.atttypmod) INTO v_coldef FROM pg_namespace n, pg_class c, pg_attribute a, pg_type t
45	WHERE n.nspname = in_schema AND n.oid = c.relnamespace AND c.relname = in_table AND a.attname = in_column and a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
46	-- RAISE NOTICE 'DEBUG: oldway=%',v_coldef;
47	ELSE
48	-- a.attrelid::regclass::text, a.attname
49	SELECT CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[]) AND EXISTS (SELECT FROM pg_attrdef ad WHERE ad.adrelid = a.attrelid AND ad.adnum = a.attnum AND
50	pg_get_expr(ad.adbin, ad.adrelid) = 'nextval(''' || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass || '''::regclass)') THEN CASE a.atttypid
51	WHEN 'int'::regtype THEN 'serial' WHEN 'int8'::regtype THEN 'bigserial' WHEN 'int2'::regtype THEN 'smallserial' END ELSE format_type(a.atttypid, a.atttypmod) END AS data_type
52	INTO v_coldef FROM pg_namespace n, pg_class c, pg_attribute a, pg_type t
53	WHERE n.nspname = in_schema AND n.oid = c.relnamespace AND c.relname = in_table AND a.attname = in_column and a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
54	-- RAISE NOTICE 'DEBUG: newway=%',v_coldef;
55
56	-- Issue#24: not implemented yet
57	-- might replace with this below to do more detailed parsing...
58	-- SELECT a.atttypid::regtype AS dt1, format_type(a.atttypid, a.atttypmod) as dt2, t.typname as dt3, CASE WHEN not(a.attnotnull) THEN True ELSE False END AS nullable,
59	-- a.attnum, a.attidentity, a.attgenerated, a.atthasdef, pg_get_expr(ad.adbin, ad.adrelid) dfltexpr
60	-- INTO v_dt1, v_dt2, v_dt3, v_nullable, v_position, v_identity, v_generated, v_hasdflt, v_dfltexpr
61	-- FROM pg_attribute a JOIN pg_class c ON (a.attrelid = c.oid) JOIN pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
62	-- WHERE c.relkind in ('r','p') AND a.attnum > 0 AND NOT a.attisdropped AND c.relnamespace::regnamespace::text = in_schema AND c.relname = in_table AND a.attname = in_column;
63	-- RAISE NOTICE 'schema=% table=% column=% dt1=% dt2=% dt3=% nullable=% pos=% identity=% generated=% HasDefault=% DeftExpr=%', in_schema, in_table, in_column, v_dt1,v_dt2,v_dt3,v_nullable,v_position,v_identity,v_generated,v_hasdflt,v_dfltexpr;
64	END IF;
65	RETURN v_coldef;
66	END;
67	$$;
68
69	-- SELECT * FROM pg_temp.pg_get_tabledef('sample', 'address', false);
70	DROP FUNCTION IF EXISTS pg_temp.pg_get_tabledef(character varying,character varying,boolean,tabledefs[]);
71	CREATE OR REPLACE FUNCTION pg_temp.pg_get_tabledef(
72	in_schema varchar,
73	in_table varchar,
74	_verbose boolean,
75	VARIADIC arr pg_temp.tabledefs[] DEFAULT '{}':: pg_temp.tabledefs[]
76	)
77	RETURNS text
78	LANGUAGE plpgsql VOLATILE
79	AS
80	$$
81	DECLARE
82	v_qualified text := '';
83	v_table_ddl text;
84	v_table_oid int;
85	v_colrec record;
86	v_constraintrec record;
87	v_trigrec record;
88	v_indexrec record;
89	v_rec record;
90	v_constraint_name text;
91	v_constraint_def text;
92	v_pkey_def text := '';
93	v_fkey_def text := '';
94	v_fkey_defs text := '';
95	v_trigger text := '';
96	v_partition_key text := '';
97	v_partbound text;
98	v_parent text;
99	v_parent_schema text;
100	v_persist text;
101	v_temp text := '';
102	v_temp2 text;
103	v_relopts text;
104	v_tablespace text;
105	v_pgversion int;
106	bSerial boolean;
107	bPartition boolean;
108	bInheritance boolean;
109	bRelispartition boolean;
110	constraintarr text[] := '{}';
111	constraintelement text;
112	bSkip boolean;
113	bVerbose boolean := False;
114	v_cnt1 integer;
115	v_cnt2 integer;
116	search_path_old text := '';
117	search_path_new text := '';
118	v_partial boolean;
119	v_pos integer;
120
121	-- assume defaults for ENUMs at the getgo
122	pkcnt int := 0;
123	fkcnt int := 0;
124	trigcnt int := 0;
125	cmtcnt int := 0;
126	pktype pg_temp.tabledefs := 'PKEY_INTERNAL';
127	fktype pg_temp.tabledefs := 'FKEYS_INTERNAL';
128	trigtype pg_temp.tabledefs := 'NO_TRIGGERS';
129	arglen integer;
130	vargs text;
131	avarg pg_temp.tabledefs;
132
133	-- exception variables
134	v_ret text;
135	v_diag1 text;
136	v_diag2 text;
137	v_diag3 text;
138	v_diag4 text;
139	v_diag5 text;
140	v_diag6 text;
141
142	BEGIN
143	SET client_min_messages = 'notice';
144	IF _verbose THEN bVerbose = True; END IF;
145
146	-- v17 fix: handle case-sensitive
147	-- v_qualified = in_schema || '.' || in_table;
148
149	arglen := array_length($4, 1);
150	IF arglen IS NULL THEN
151	-- nothing to do, so assume defaults
152	NULL;
153	ELSE
154	-- loop thru args
155	-- IF 'NO_TRIGGERS' = ANY ($4)
156	-- select array_to_string($4, ',', '***') INTO vargs;
157	IF bVerbose THEN RAISE NOTICE 'arguments=%', $4; END IF;
158	FOREACH avarg IN ARRAY $4 LOOP
159	IF bVerbose THEN RAISE NOTICE 'arg=%', avarg; END IF;
160	IF avarg = 'FKEYS_INTERNAL' OR avarg = 'FKEYS_EXTERNAL' OR avarg = 'FKEYS_NONE' THEN
161	fkcnt = fkcnt + 1;
162	fktype = avarg;
163	ELSEIF avarg = 'INCLUDE_TRIGGERS' OR avarg = 'NO_TRIGGERS' THEN
164	trigcnt = trigcnt + 1;
165	trigtype = avarg;
166	ELSEIF avarg = 'PKEY_EXTERNAL' THEN
167	pkcnt = pkcnt + 1;
168	pktype = avarg;
169	ELSEIF avarg = 'COMMENTS' THEN
170	cmtcnt = cmtcnt + 1;
171
172	END IF;
173	END LOOP;
174	IF fkcnt > 1 THEN
175	RAISE WARNING 'Only one foreign key option can be provided. You provided %', fkcnt;
176	RETURN '';
177	ELSEIF trigcnt > 1 THEN
178	RAISE WARNING 'Only one trigger option can be provided. You provided %', trigcnt;
179	RETURN '';
180	ELSEIF pkcnt > 1 THEN
181	RAISE WARNING 'Only one pkey option can be provided. You provided %', pkcnt;
182	RETURN '';
183	ELSEIF cmtcnt > 1 THEN
184	RAISE WARNING 'Only one comments option can be provided. You provided %', cmtcnt;
185	RETURN '';
186
187	END IF;
188	END IF;
189
190	SELECT c.oid, (select setting from pg_settings where name = 'server_version_num') INTO v_table_oid, v_pgversion FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
191	WHERE c.relkind in ('r','p') AND c.relname = in_table AND n.nspname = in_schema;
192
193	-- set search_path = public before we do anything to force explicit schema qualification but dont forget to set it back before exiting...
194	SELECT setting INTO search_path_old FROM pg_settings WHERE name = 'search_path';
195
196	-- RAISE NOTICE 'DEBUG tableddl: saving old search_path: ***%***', search_path_old;
197	EXECUTE 'SET search_path = "public"';
198	SELECT setting INTO search_path_new FROM pg_settings WHERE name = 'search_path';
199	-- RAISE NOTICE 'DEBUG tableddl: using new search path=***%***', search_path_new;
200
201	-- throw an error if table was not found
202	IF (v_table_oid IS NULL) THEN
203	RAISE EXCEPTION 'table does not exist';
204	END IF;
205
206	-- get user-defined tablespaces if applicable
207	SELECT tablespace INTO v_temp FROM pg_tables WHERE schemaname = in_schema and tablename = in_table and tablespace IS NOT NULL;
208	IF v_temp IS NULL THEN
209	v_tablespace := 'TABLESPACE pg_default';
210	ELSE
211	v_tablespace := 'TABLESPACE ' || v_temp;
212	END IF;
213
214	-- also see if there are any SET commands for this table, ie, autovacuum_enabled=off, fillfactor=70
215	WITH relopts AS (SELECT unnest(c.reloptions) relopts FROM pg_class c, pg_namespace n WHERE n.nspname = in_schema and n.oid = c.relnamespace and c.relname = in_table)
216	SELECT string_agg(r.relopts, ', ') as relopts INTO v_temp from relopts r;
217	IF v_temp IS NULL THEN
218	v_relopts := '';
219	ELSE
220	v_relopts := ' WITH (' || v_temp || ')';
221	END IF;
222
223	-- -----------------------------------------------------------------------------------
224	-- Create table defs for partitions/children using inheritance or declarative methods.
225	-- inheritance: pg_class.relkind = 'r' pg_class.relispartition=false pg_class.relpartbound is NULL
226	-- declarative: pg_class.relkind = 'r' pg_class.relispartition=true pg_class.relpartbound is NOT NULL
227	-- -----------------------------------------------------------------------------------
228	v_partbound := '';
229	bPartition := False;
230	bInheritance := False;
231	IF v_pgversion < 100000 THEN
232	-- Issue#11: handle parent schema
233	SELECT c2.relname parent, c2.relnamespace::regnamespace INTO v_parent, v_parent_schema from pg_class c1, pg_namespace n, pg_inherits i, pg_class c2
234	WHERE n.nspname = in_schema and n.oid = c1.relnamespace and c1.relname = in_table and c1.oid = i.inhrelid and i.inhparent = c2.oid and c1.relkind = 'r';
235	IF (v_parent IS NOT NULL) THEN
236	bPartition := True;
237	bInheritance := True;
238	END IF;
239	ELSE
240	-- Issue#11: handle parent schema
241	SELECT c2.relname parent, c1.relispartition, pg_get_expr(c1.relpartbound, c1.oid, true), c2.relnamespace::regnamespace INTO v_parent, bRelispartition, v_partbound, v_parent_schema from pg_class c1, pg_namespace n, pg_inherits i, pg_class c2
242	WHERE n.nspname = in_schema and n.oid = c1.relnamespace and c1.relname = in_table and c1.oid = i.inhrelid and i.inhparent = c2.oid and c1.relkind = 'r';
243	IF (v_parent IS NOT NULL) THEN
244	bPartition := True;
245	IF bRelispartition THEN
246	bInheritance := False;
247	ELSE
248	bInheritance := True;
249	END IF;
250	END IF;
251	END IF;
252	IF bPartition THEN
253	--Issue#17 fix for case-sensitive tables
254	-- SELECT count(*) INTO v_cnt1 FROM information_schema.tables t WHERE EXISTS (SELECT REGEXP_MATCHES(s.table_name, '([A-Z]+)','g') FROM information_schema.tables s
255	-- WHERE t.table_schema=s.table_schema AND t.table_name=s.table_name AND t.table_schema = quote_ident(in_schema) AND t.table_name = quote_ident(in_table) AND t.table_type = 'BASE TABLE');
256	SELECT count(*) INTO v_cnt1 FROM information_schema.tables t WHERE EXISTS (SELECT REGEXP_MATCHES(s.table_name, '([A-Z]+)','g') FROM information_schema.tables s
257	WHERE t.table_schema=s.table_schema AND t.table_name=s.table_name AND t.table_schema = in_schema AND t.table_name = in_table AND t.table_type = 'BASE TABLE');
258
259	--Issue#19 put double-quotes around SQL keyword column names
260	-- Issue#121: fix keyword lookup for table name not column name that does not apply here
261	-- SELECT COUNT(*) INTO v_cnt2 FROM pg_get_keywords() WHERE word = v_colrec.column_name AND catcode = 'R';
262	SELECT COUNT(*) INTO v_cnt2 FROM pg_get_keywords() WHERE word = in_table AND catcode = 'R';
263
264	IF bInheritance THEN
265	-- inheritance-based
266	IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
267	v_table_ddl := 'CREATE TABLE ' || in_schema || '."' || in_table || '"( '|| E'\\n';
268	ELSE
269	v_table_ddl := 'CREATE TABLE ' || in_schema || '.' || in_table || '( '|| E'\\n';
270	END IF;
271
272	-- Jump to constraints section to add the check constraints
273	ELSE
274	-- declarative-based
275	IF v_relopts <> '' THEN
276	IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
277	v_table_ddl := 'CREATE TABLE ' || in_schema || '."' || in_table || '" PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || v_relopts || ' ' || v_tablespace || '; ' || E'\\n';
278	ELSE
279	v_table_ddl := 'CREATE TABLE ' || in_schema || '.' || in_table || ' PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || v_relopts || ' ' || v_tablespace || '; ' || E'\\n';
280	END IF;
281	ELSE
282	IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
283	v_table_ddl := 'CREATE TABLE ' || in_schema || '."' || in_table || '" PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || ' ' || v_tablespace || '; ' || E'\\n';
284	ELSE
285	v_table_ddl := 'CREATE TABLE ' || in_schema || '.' || in_table || ' PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || ' ' || v_tablespace || '; ' || E'\\n';
286	END IF;
287	END IF;
288	-- Jump to constraints and index section to add the check constraints and indexes and perhaps FKeys
289	END IF;
290	END IF;
291	IF bVerbose THEN RAISE NOTICE '(1)tabledef so far: %', v_table_ddl; END IF;
292
293	IF NOT bPartition THEN
294	-- see if this is unlogged or temporary table
295	select c.relpersistence into v_persist from pg_class c, pg_namespace n where n.nspname = in_schema and n.oid = c.relnamespace and c.relname = in_table and c.relkind = 'r';
296	IF v_persist = 'u' THEN
297	v_temp := 'UNLOGGED';
298	ELSIF v_persist = 't' THEN
299	v_temp := 'TEMPORARY';
300	ELSE
301	v_temp := '';
302	END IF;
303	END IF;
304
305	-- start the create definition for regular tables unless we are in progress creating an inheritance-based child table
306	IF NOT bPartition THEN
307	--Issue#17 fix for case-sensitive tables
308	-- SELECT count(*) INTO v_cnt1 FROM information_schema.tables t WHERE EXISTS (SELECT REGEXP_MATCHES(s.table_name, '([A-Z]+)','g') FROM information_schema.tables s
309	-- WHERE t.table_schema=s.table_schema AND t.table_name=s.table_name AND t.table_schema = quote_ident(in_schema) AND t.table_name = quote_ident(in_table) AND t.table_type = 'BASE TABLE');
310	SELECT count(*) INTO v_cnt1 FROM information_schema.tables t WHERE EXISTS (SELECT REGEXP_MATCHES(s.table_name, '([A-Z]+)','g') FROM information_schema.tables s
311	WHERE t.table_schema=s.table_schema AND t.table_name=s.table_name AND t.table_schema = in_schema AND t.table_name = in_table AND t.table_type = 'BASE TABLE');
312	IF v_cnt1 > 0 THEN
313	v_table_ddl := 'CREATE ' || v_temp || ' TABLE ' || in_schema || '."' || in_table || '" (' || E'\\n';
314	ELSE
315	v_table_ddl := 'CREATE ' || v_temp || ' TABLE ' || in_schema || '.' || in_table || ' (' || E'\\n';
316	END IF;
317	END IF;
318	-- RAISE NOTICE 'DEBUG2: tabledef so far: %', v_table_ddl;
319	-- define all of the columns in the table unless we are in progress creating an inheritance-based child table
320	IF NOT bPartition THEN
321	FOR v_colrec IN
322	SELECT c.column_name, c.data_type, c.udt_name, c.udt_schema, c.character_maximum_length, c.is_nullable, c.column_default, c.numeric_precision, c.numeric_scale, c.is_identity, c.identity_generation, c.is_generated, c.generation_expression
323	FROM information_schema.columns c WHERE (table_schema, table_name) = (in_schema, in_table) ORDER BY ordinal_position
324	LOOP
325	IF bVerbose THEN RAISE NOTICE '(col loop) name=% type=% udt_name=% default=% is_generated=% gen_expr=%', v_colrec.column_name, v_colrec.data_type, v_colrec.udt_name, v_colrec.column_default, v_colrec.is_generated, v_colrec.generation_expression; END IF;
326
327	-- v17 fix: handle case-sensitive for pg_get_serial_sequence that requires SQL Identifier handling
328	-- SELECT CASE WHEN pg_get_serial_sequence(v_qualified, v_colrec.column_name) IS NOT NULL THEN True ELSE False END into bSerial;
329	SELECT CASE WHEN pg_get_serial_sequence(quote_ident(in_schema) || '.' || quote_ident(in_table), v_colrec.column_name) IS NOT NULL THEN True ELSE False END into bSerial;
330	IF bVerbose THEN
331	-- v17 fix: handle case-sensitive for pg_get_serial_sequence that requires SQL Identifier handling
332	-- SELECT pg_get_serial_sequence(v_qualified, v_colrec.column_name) into v_temp;
333	SELECT pg_get_serial_sequence(quote_ident(in_schema) || '.' || quote_ident(in_table), v_colrec.column_name) into v_temp;
334	IF v_temp IS NULL THEN v_temp = 'NA'; END IF;
335	SELECT pg_temp.pg_get_coldef(in_schema, in_table,v_colrec.column_name) INTO v_diag1;
336	RAISE NOTICE 'DEBUG table: % Column: % datatype: % Serial=% serialval=% coldef=%', v_qualified, v_colrec.column_name, v_colrec.data_type, bSerial, v_temp, v_diag1;
337	RAISE NOTICE 'DEBUG tabledef: %', v_table_ddl;
338	END IF;
339
340	--Issue#17 put double-quotes around case-sensitive column names
341	SELECT COUNT(*) INTO v_cnt1 FROM information_schema.columns t WHERE EXISTS (SELECT REGEXP_MATCHES(s.column_name, '([A-Z]+)','g') FROM information_schema.columns s
342	WHERE t.table_schema=s.table_schema and t.table_name=s.table_name and t.column_name=s.column_name AND t.table_schema = quote_ident(in_schema) AND column_name = v_colrec.column_name);
343
344	--Issue#19 put double-quotes around SQL keyword column names
345	SELECT COUNT(*) INTO v_cnt2 FROM pg_get_keywords() WHERE word = v_colrec.column_name AND catcode = 'R';
346
347	IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
348	v_table_ddl := v_table_ddl || ' "' || v_colrec.column_name || '" ';
349	ELSE
350	v_table_ddl := v_table_ddl || ' ' || v_colrec.column_name || ' ';
351	END IF;
352
353	-- Issue#23: Handle autogenerated columns and rewrite as a simpler IF THEN ELSE branch instead of a much more complex embedded CASE STATEMENT
354	IF v_colrec.is_generated = 'ALWAYS' and v_colrec.generation_expression IS NOT NULL THEN
355	-- searchable tsvector GENERATED ALWAYS AS (to_tsvector('simple'::regconfig, COALESCE(translate(email, '@.-'::citext, ' '::text), ''::text)) ) STORED
356	v_temp = v_colrec.data_type || ' GENERATED ALWAYS AS (' || v_colrec.generation_expression || ') STORED ';
357	ELSEIF v_colrec.udt_name in ('geometry', 'box2d', 'box2df', 'box3d', 'geography', 'geometry_dump', 'gidx', 'spheroid', 'valid_detail') THEN
358	v_temp = v_colrec.udt_name;
359	ELSEIF v_colrec.data_type = 'USER-DEFINED' THEN
360	v_temp = v_colrec.udt_schema || '.' || v_colrec.udt_name;
361	ELSEIF v_colrec.data_type = 'ARRAY' THEN
362	-- Issue#6 fix: handle arrays
363	v_temp = pg_temp.pg_get_coldef(in_schema, in_table,v_colrec.column_name);
364	-- v17 fix: handle case-sensitive for pg_get_serial_sequence that requires SQL Identifier handling
365	-- WHEN pg_get_serial_sequence(v_qualified, v_colrec.column_name) IS NOT NULL
366	ELSEIF pg_get_serial_sequence(quote_ident(in_schema) || '.' || quote_ident(in_table), v_colrec.column_name) IS NOT NULL THEN
367	-- Issue#8 fix: handle serial. Note: NOT NULL is implied so no need to declare it explicitly
368	v_temp = pg_temp.pg_get_coldef(in_schema, in_table,v_colrec.column_name);
369	ELSE
370	v_temp = v_colrec.data_type;
371	END IF;
372	-- RAISE NOTICE 'column def1=%', v_temp;
373
374	-- handle IDENTITY columns
375	IF v_colrec.is_identity = 'YES' THEN
376	IF v_colrec.identity_generation = 'ALWAYS' THEN
377	v_temp = v_temp || ' GENERATED ALWAYS AS IDENTITY';
378	ELSE
379	v_temp = v_temp || ' GENERATED BY DEFAULT AS IDENTITY';
380	END IF;
381	ELSEIF v_colrec.character_maximum_length IS NOT NULL THEN
382	v_temp = v_temp || ('(' || v_colrec.character_maximum_length || ')');
383	ELSEIF v_colrec.numeric_precision > 0 AND v_colrec.numeric_scale > 0 THEN
384	v_temp = v_temp || '(' || v_colrec.numeric_precision || ',' || v_colrec.numeric_scale || ')';
385	END IF;
386
387	-- Handle NULL/NOT NULL
388	IF bSerial THEN
389	v_temp = v_temp || ' NOT NULL';
390	ELSEIF v_colrec.is_nullable = 'NO' THEN
391	v_temp = v_temp || ' NOT NULL';
392	ELSEIF v_colrec.is_nullable = 'YES' THEN
393	v_temp = v_temp || ' NULL';
394	END IF;
395
396	-- Handle defaults
397	IF v_colrec.column_default IS NOT null AND NOT bSerial THEN
398	-- RAISE NOTICE 'Setting default for column, %', v_colrec.column_name;
399	v_temp = v_temp || (' DEFAULT ' || v_colrec.column_default);
400	END IF;
401	v_temp = v_temp || ',' || E'\\n';
402	-- RAISE NOTICE 'column def2=%', v_temp;
403	v_table_ddl := v_table_ddl || v_temp;
404	-- RAISE NOTICE 'tabledef=%', v_table_ddl;
405
406	END LOOP;
407	END IF;
408	IF bVerbose THEN RAISE NOTICE '(2)tabledef so far: %', v_table_ddl; END IF;
409
410	-- define all the constraints: conparentid does not exist pre PGv11
411	IF v_pgversion < 110000 THEN
412	FOR v_constraintrec IN
413	SELECT con.conname as constraint_name, con.contype as constraint_type,
414	CASE
415	WHEN con.contype = 'p' THEN 1 -- primary key constraint
416	WHEN con.contype = 'u' THEN 2 -- unique constraint
417	WHEN con.contype = 'f' THEN 3 -- foreign key constraint
418	WHEN con.contype = 'c' THEN 4
419	ELSE 5
420	END as type_rank,
421	pg_get_constraintdef(con.oid) as constraint_definition
422	FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
423	WHERE nsp.nspname = in_schema AND rel.relname = in_table ORDER BY type_rank
424	LOOP
425	v_constraint_name := v_constraintrec.constraint_name;
426	v_constraint_def := v_constraintrec.constraint_definition;
427	IF v_constraintrec.type_rank = 1 THEN
428	IF pkcnt = 0 OR pktype = 'PKEY_INTERNAL' THEN
429	-- internal def
430	v_constraint_name := v_constraintrec.constraint_name;
431	v_constraint_def := v_constraintrec.constraint_definition;
432	v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
433	|| 'CONSTRAINT' || ' '
434	|| v_constraint_name || ' '
435	|| v_constraint_def
436	|| ',' || E'\\n';
437	ELSE
438	-- Issue#16 handle external PG def
439	SELECT 'ALTER TABLE ONLY ' || in_schema || '.' || c.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_pkey_def
440	FROM pg_catalog.pg_constraint r, pg_class c, pg_namespace n where r.conrelid = c.oid and r.contype = 'p' and n.oid = r.connamespace and n.nspname = in_schema AND c.relname = in_table and r.conname = v_constraint_name;
441	END IF;
442	IF bPartition THEN
443	continue;
444	END IF;
445	ELSIF v_constraintrec.type_rank = 3 THEN
446	-- handle foreign key constraints
447	--Issue#22 fix: added FKEY_NONE check
448	IF fktype = 'FKEYS_NONE' THEN
449	-- skip
450	continue;
451	ELSIF fkcnt = 0 OR fktype = 'FKEYS_INTERNAL' THEN
452	-- internal def
453	v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
454	|| 'CONSTRAINT' || ' '
455	|| v_constraint_name || ' '
456	|| v_constraint_def
457	|| ',' || E'\\n';
458	ELSE
459	-- external def
460	SELECT 'ALTER TABLE ONLY ' || n.nspname || '.' || c2.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_fkey_def
461	FROM pg_constraint r, pg_class c1, pg_namespace n, pg_class c2 where r.conrelid = c1.oid and r.contype = 'f' and n.nspname = in_schema and n.oid = r.connamespace and r.conrelid = c2.oid and c2.relname = in_table;
462	v_fkey_defs = v_fkey_defs || v_fkey_def || E'\\n';
463	END IF;
464	ELSE
465	-- handle all other constraints besides PKEY and FKEYS as internal defs by default
466	v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
467	|| 'CONSTRAINT' || ' '
468	|| v_constraint_name || ' '
469	|| v_constraint_def
470	|| ',' || E'\\n';
471	END IF;
472	if bVerbose THEN RAISE NOTICE 'DEBUG4: constraint name=% constraint_def=%', v_constraint_name,v_constraint_def; END IF;
473	constraintarr := constraintarr || v_constraintrec.constraint_name:: text;
474
475	END LOOP;
476	ELSE
477	-- handle PG versions 11 and up
478	-- Issue#20: Fix logic for external PKEY and FKEYS
479	FOR v_constraintrec IN
480	SELECT con.conname as constraint_name, con.contype as constraint_type,
481	CASE
482	WHEN con.contype = 'p' THEN 1 -- primary key constraint
483	WHEN con.contype = 'u' THEN 2 -- unique constraint
484	WHEN con.contype = 'f' THEN 3 -- foreign key constraint
485	WHEN con.contype = 'c' THEN 4
486	ELSE 5
487	END as type_rank,
488	pg_get_constraintdef(con.oid) as constraint_definition
489	FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
490	WHERE nsp.nspname = in_schema AND rel.relname = in_table
491	--Issue#13 added this condition:
492	AND con.conparentid = 0
493	ORDER BY type_rank
494	LOOP
495	v_constraint_name := v_constraintrec.constraint_name;
496	v_constraint_def := v_constraintrec.constraint_definition;
497	IF v_constraintrec.type_rank = 1 THEN
498	IF pkcnt = 0 OR pktype = 'PKEY_INTERNAL' THEN
499	-- internal def
500	v_constraint_name := v_constraintrec.constraint_name;
501	v_constraint_def := v_constraintrec.constraint_definition;
502	v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
503	|| 'CONSTRAINT' || ' '
504	|| v_constraint_name || ' '
505	|| v_constraint_def
506	|| ',' || E'\\n';
507	ELSE
508	-- Issue#16 handle external PG def
509	SELECT 'ALTER TABLE ONLY ' || in_schema || '.' || c.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_pkey_def
510	FROM pg_catalog.pg_constraint r, pg_class c, pg_namespace n where r.conrelid = c.oid and r.contype = 'p' and n.oid = r.connamespace and n.nspname = in_schema AND c.relname = in_table;
511	END IF;
512	IF bPartition THEN
513	continue;
514	END IF;
515	ELSIF v_constraintrec.type_rank = 3 THEN
516	-- handle foreign key constraints
517	--Issue#22 fix: added FKEY_NONE check
518	IF fktype = 'FKEYS_NONE' THEN
519	-- skip
520	continue;
521	ELSIF fkcnt = 0 OR fktype = 'FKEYS_INTERNAL' THEN
522	-- internal def
523	v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
524	|| 'CONSTRAINT' || ' '
525	|| v_constraint_name || ' '
526	|| v_constraint_def
527	|| ',' || E'\\n';
528	ELSE
529	-- external def
530	SELECT 'ALTER TABLE ONLY ' || n.nspname || '.' || c2.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_fkey_def
531	FROM pg_constraint r, pg_class c1, pg_namespace n, pg_class c2 where r.conrelid = c1.oid and r.contype = 'f' and n.nspname = in_schema and n.oid = r.connamespace and r.conrelid = c2.oid and c2.relname = in_table and
532	r.conname = v_constraint_name and r.conparentid = 0;
533	v_fkey_defs = v_fkey_defs || v_fkey_def || E'\\n';
534	END IF;
535	ELSE
536	-- handle all other constraints besides PKEY and FKEYS as internal defs by default
537	v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
538	|| 'CONSTRAINT' || ' '
539	|| v_constraint_name || ' '
540	|| v_constraint_def
541	|| ',' || E'\\n';
542	END IF;
543	if bVerbose THEN RAISE NOTICE 'DEBUG4: constraint name=% constraint_def=%', v_constraint_name,v_constraint_def; END IF;
544	constraintarr := constraintarr || v_constraintrec.constraint_name:: text;
545
546	END LOOP;
547	END IF;
548
549	-- drop the last comma before ending the create statement, which should be right before the carriage return character
550	-- Issue#24: make sure the comma is there before removing it
551	select substring(v_table_ddl, length(v_table_ddl) - 1, 1) INTO v_temp;
552	IF v_temp = ',' THEN
553	v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\\n';
554	END IF;
555	IF bVerbose THEN RAISE NOTICE '(3)tabledef so far: %', trim(v_table_ddl); END IF;
556
557	-- ---------------------------------------------------------------------------
558	-- at this point we have everything up to the last table-enclosing parenthesis
559	-- ---------------------------------------------------------------------------
560	IF bVerbose THEN RAISE NOTICE '(4)tabledef so far: %', v_table_ddl; END IF;
561
562	-- See if this is an inheritance-based child table and finish up the table create.
563	IF bPartition and bInheritance THEN
564	-- Issue#11: handle parent schema
565	-- v_table_ddl := v_table_ddl || ') INHERITS (' || in_schema || '.' || v_parent || ') ' || E'\\n' || v_relopts || ' ' || v_tablespace || ';' || E'\\n';
566	IF v_parent_schema = '' OR v_parent_schema IS NULL THEN v_parent_schema = in_schema; END IF;
567	v_table_ddl := v_table_ddl || ') INHERITS (' || v_parent_schema || '.' || v_parent || ') ' || E'\\n' || v_relopts || ' ' || v_tablespace || ';' || E'\\n';
568	END IF;
569
570	IF v_pgversion >= 100000 AND NOT bPartition and NOT bInheritance THEN
571	-- See if this is a partitioned table (pg_class.relkind = 'p') and add the partitioned key
572	SELECT pg_get_partkeydef(c1.oid) as partition_key INTO v_partition_key FROM pg_class c1 JOIN pg_namespace n ON (n.oid = c1.relnamespace) LEFT JOIN pg_partitioned_table p ON (c1.oid = p.partrelid)
573	WHERE n.nspname = in_schema and n.oid = c1.relnamespace and c1.relname = in_table and c1.relkind = 'p';
574
575	IF v_partition_key IS NOT NULL AND v_partition_key <> '' THEN
576	-- add partition clause
577	-- NOTE: cannot specify default tablespace for partitioned relations
578	-- v_table_ddl := v_table_ddl || ') PARTITION BY ' || v_partition_key || ' ' || v_tablespace || ';' || E'\\n';
579	v_table_ddl := v_table_ddl || ') PARTITION BY ' || v_partition_key || ';' || E'\\n';
580	ELSEIF v_relopts <> '' THEN
581	v_table_ddl := v_table_ddl || ') ' || v_relopts || ' ' || v_tablespace || ';' || E'\\n';
582	ELSE
583	-- end the create definition
584	v_table_ddl := v_table_ddl || ') ' || v_tablespace || ';' || E'\\n';
585	END IF;
586	END IF;
587
588	IF bVerbose THEN RAISE NOTICE '(5)tabledef so far: %', v_table_ddl; END IF;
589
590	-- Add closing paren for regular tables
591	-- IF NOT bPartition THEN
592	-- v_table_ddl := v_table_ddl || ') ' || v_relopts || ' ' || v_tablespace || E';\\n';
593	-- END IF;
594	-- RAISE NOTICE 'ddlsofar3: %', v_table_ddl;
595
596	-- Issue#16 create the external PKEY def if indicated
597	IF v_pkey_def <> '' THEN
598	v_table_ddl := v_table_ddl || v_pkey_def || E'\\n';
599	END IF;
600
601	-- Issue#20
602	IF v_fkey_defs <> '' THEN
603	v_table_ddl := v_table_ddl || v_fkey_defs || E'\\n';
604	END IF;
605
606	IF bVerbose THEN RAISE NOTICE '(6)tabledef so far: %', v_table_ddl; END IF;
607
608	-- create indexes
609	FOR v_indexrec IN
610	SELECT indexdef, COALESCE(tablespace, 'pg_default') as tablespace, indexname FROM pg_indexes WHERE (schemaname, tablename) = (in_schema, in_table)
611	LOOP
612	-- RAISE NOTICE 'DEBUG6: indexname=% indexdef=%', v_indexrec.indexname, v_indexrec.indexdef;
613	-- loop through constraints and skip ones already defined
614	bSkip = False;
615	FOREACH constraintelement IN ARRAY constraintarr
616	LOOP
617	IF constraintelement = v_indexrec.indexname THEN
618	-- RAISE NOTICE 'DEBUG7: skipping index, %', v_indexrec.indexname;
619	bSkip = True;
620	EXIT;
621	END IF;
622	END LOOP;
623	if bSkip THEN CONTINUE; END IF;
624
625	-- Add IF NOT EXISTS clause so partition index additions will not be created if declarative partition in effect and index already created on parent
626	v_indexrec.indexdef := REPLACE(v_indexrec.indexdef, 'CREATE INDEX', 'CREATE INDEX IF NOT EXISTS');
627	-- Fix Issue#26: do it for unique/primary key indexes as well
628	v_indexrec.indexdef := REPLACE(v_indexrec.indexdef, 'CREATE UNIQUE INDEX', 'CREATE UNIQUE INDEX IF NOT EXISTS');
629	-- RAISE NOTICE 'DEBUG8: adding index, %', v_indexrec.indexname;
630
631	-- NOTE: cannot specify default tablespace for partitioned relations
632	IF v_partition_key IS NOT NULL AND v_partition_key <> '' THEN
633	v_table_ddl := v_table_ddl || v_indexrec.indexdef || ';' || E'\\n';
634	ELSE
635	-- Issue#25: see if partial index or not
636	select CASE WHEN i.indpred IS NOT NULL THEN True ELSE False END INTO v_partial
637	FROM pg_index i JOIN pg_class c1 ON (i.indexrelid = c1.oid) JOIN pg_class c2 ON (i.indrelid = c2.oid)
638	WHERE c1.relnamespace::regnamespace::text = in_schema AND c2.relnamespace::regnamespace::text = in_schema AND c2.relname = in_table AND c1.relname = v_indexrec.indexname;
639	IF v_partial THEN
640	-- Put tablespace def before WHERE CLAUSE
641	v_temp = v_indexrec.indexdef;
642	v_pos = POSITION(' WHERE ' IN v_temp);
643	v_temp2 = SUBSTRING(v_temp, v_pos);
644	v_temp = SUBSTRING(v_temp, 1, v_pos);
645	v_table_ddl := v_table_ddl || v_temp || ' TABLESPACE ' || v_indexrec.tablespace || v_temp2 || ';' || E'\\n';
646	ELSE
647	v_table_ddl := v_table_ddl || v_indexrec.indexdef || ' TABLESPACE ' || v_indexrec.tablespace || ';' || E'\\n';
648	END IF;
649	END IF;
650
651	END LOOP;
652	IF bVerbose THEN RAISE NOTICE '(7)tabledef so far: %', v_table_ddl; END IF;
653
654	-- Issue#20: added logic for table and column comments
655	IF cmtcnt > 0 THEN
656	FOR v_rec IN
657	SELECT c.relname, 'COMMENT ON ' || CASE WHEN c.relkind in ('r','p') AND a.attname IS NULL THEN 'TABLE ' WHEN c.relkind in ('r','p') AND a.attname IS NOT NULL THEN 'COLUMN ' WHEN c.relkind = 'f' THEN 'FOREIGN TABLE '
658	WHEN c.relkind = 'm' THEN 'MATERIALIZED VIEW ' WHEN c.relkind = 'v' THEN 'VIEW ' WHEN c.relkind = 'i' THEN 'INDEX ' WHEN c.relkind = 'S' THEN 'SEQUENCE ' ELSE 'XX' END || n.nspname || '.' ||
659	CASE WHEN c.relkind in ('r','p') AND a.attname IS NOT NULL THEN quote_ident(c.relname) || '.' || a.attname ELSE quote_ident(c.relname) END || ' IS ' || quote_literal(d.description) || ';' as ddl
660	FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) LEFT JOIN pg_description d ON (c.oid = d.objoid) LEFT JOIN pg_attribute a ON (c.oid = a.attrelid AND a.attnum > 0 and a.attnum = d.objsubid)
661	WHERE d.description IS NOT NULL AND n.nspname = in_schema AND c.relname = in_table ORDER BY 2 desc, ddl
662	LOOP
663	--RAISE NOTICE 'comments:%', v_rec.ddl;
664	v_table_ddl = v_table_ddl || v_rec.ddl || E'\\n';
665	END LOOP;
666	END IF;
667	IF bVerbose THEN RAISE NOTICE '(8)tabledef so far: %', v_table_ddl; END IF;
668
669	IF trigtype = 'INCLUDE_TRIGGERS' THEN
670	-- Issue#14: handle multiple triggers for a table
671	FOR v_trigrec IN
672	select pg_get_triggerdef(t.oid, True) || ';' as triggerdef FROM pg_trigger t, pg_class c, pg_namespace n
673	WHERE n.nspname = in_schema and n.oid = c.relnamespace and c.relname = in_table and c.relkind = 'r' and t.tgrelid = c.oid and NOT t.tgisinternal
674	LOOP
675	v_table_ddl := v_table_ddl || v_trigrec.triggerdef;
676	v_table_ddl := v_table_ddl || E'\\n';
677	IF bVerbose THEN RAISE NOTICE 'triggerdef = %', v_trigrec.triggerdef; END IF;
678	END LOOP;
679	END IF;
680
681	IF bVerbose THEN RAISE NOTICE '(9)tabledef so far: %', v_table_ddl; END IF;
682	-- add empty line
683	v_table_ddl := v_table_ddl || E'\\n';
684	IF bVerbose THEN RAISE NOTICE '(10)tabledef so far: %', v_table_ddl; END IF;
685
686	-- reset search_path back to what it was
687	IF search_path_old = '' THEN
688	SELECT set_config('search_path', '', false) into v_temp;
689	ELSE
690	EXECUTE 'SET search_path = ' || search_path_old;
691	END IF;
692
693	RETURN v_table_ddl;
694
695	EXCEPTION
696	WHEN others THEN
697	BEGIN
698	GET STACKED DIAGNOSTICS v_diag1 = MESSAGE_TEXT, v_diag2 = PG_EXCEPTION_DETAIL, v_diag3 = PG_EXCEPTION_HINT, v_diag4 = RETURNED_SQLSTATE, v_diag5 = PG_CONTEXT, v_diag6 = PG_EXCEPTION_CONTEXT;
699	-- v_ret := 'line=' || v_diag6 || '. '|| v_diag4 || '. ' || v_diag1 || ' .' || v_diag2 || ' .' || v_diag3;
700	v_ret := 'line=' || v_diag6 || '. '|| v_diag4 || '. ' || v_diag1;
701	RAISE EXCEPTION '%', v_ret;
702	-- put additional coding here if necessarY
703	RETURN '';
704	END;
705
706	END;
707	$$;`
708
709	export const getTableDefinitionSql = ({ id }: { id: number }): SafeSqlFragment => {
710	const sql = safeSql`
711	${CREATE_PG_GET_TABLEDEF_SQL}
712
713	with table_info as (
714	select
715	n.nspname::text as schema,
716	c.relname::text as name
717	from pg_class c
718	join pg_namespace n on n.oid = c.relnamespace
719	where c.oid = ${literal(id)}
720	)
721	select pg_temp.pg_get_tabledef (
722	t.schema,
723	t.name,
724	false,
725	'FKEYS_INTERNAL',
726	'INCLUDE_TRIGGERS'
727	) as definition
728	from table_info t;
729	`
730
731	return sql
732	}
733
734	export const getEntityDefinitionsSql = ({
735	schemas,
736	limit = 100,
737	}: {
738	schemas: string[]
739	limit?: number
740	}): SafeSqlFragment => {
741	const sql = safeSql`
742	${CREATE_PG_GET_TABLEDEF_SQL}
743
744	with records as (
745	select
746	c.oid::int8 as "id",
747	case c.relkind
748	when 'r' then pg_temp.pg_get_tabledef(
749	concat(nc.nspname),
750	concat(c.relname),
751	false,
752	'FKEYS_INTERNAL',
753	'NO_TRIGGERS'
754	)
755	when 'v' then concat(
756	'create view ', concat(nc.nspname, '.', c.relname), ' as',
757	pg_get_viewdef(concat(nc.nspname, '.', c.relname), true)
758	)
759	when 'm' then concat(
760	'create materialized view ', concat(nc.nspname, '.', c.relname), ' as',
761	pg_get_viewdef(concat(nc.nspname, '.', c.relname), true)
762	)
763	when 'f' then concat('create foreign table ', nc.nspname, '.', c.relname, ' ( ... )')
764	when 'p' then pg_temp.pg_get_tabledef(
765	concat(nc.nspname),
766	concat(c.relname),
767	false,
768	'FKEYS_INTERNAL',
769	'NO_TRIGGERS'
770	)
771	end as "sql"
772	from
773	pg_namespace nc
774	join pg_class c on nc.oid = c.relnamespace
775	where
776	c.relkind in ('r', 'v', 'm', 'f', 'p')
777	and not pg_is_other_temp_schema(nc.oid)
778	and (
779	pg_has_role(c.relowner, 'USAGE')
780	or has_table_privilege(
781	c.oid,
782	'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'
783	)
784	or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
785	)
786	and nc.nspname IN (${joinSqlFragments(
787	schemas.map((schema) => literal(schema)),
788	', '
789	)})
790	order by c.relname asc
791	limit ${literal(limit)}
792	offset 0
793	)
794	select
795	jsonb_build_object(
796	'definitions', coalesce(jsonb_agg(
797	jsonb_build_object(
798	'id', r.id,
799	'sql', r.sql
800	)
801	), '[]'::jsonb)
802	) "data"
803	from records r;
804	`
805
806	return sql
807	}