-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathpg_get_tabledef.sql
1003 lines (908 loc) · 58.6 KB
/
pg_get_tabledef.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/* ********************************************************************************
COPYRIGHT NOTICE FOLLOWS. DO NOT REMOVE
Copyright (c) 2021-2025 SQLEXEC LLC
GNU General Public License v3.0
Permission to use, copy, modify, and distribute this software and its documentation
for any purpose, without fee, and without a written agreement is hereby granted,
provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL SQLEXEC LLC BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,INDIRECT SPECIAL,
INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE
OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF SQLEXEC LLC HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
SQLEXEC LLC SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND SQLEXEC LLC HAS
NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
************************************************************************************ */
-- History:
-- Date Description
-- ========== ======================================================================
-- 2021-03-20 Original coding using some snippets from
-- https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr
-- 2021-03-21 -------- Added partitioned table support, i.e., PARTITION BY clause.
-- 2021-03-21 -------- Added WITH clause logic where storage parameters for tables are set.
-- 2021-03-22 -------- Added tablespace logic for tables and indexes.
-- 2021-03-24 -------- Added inheritance-based partitioning support for PG 9.6 and lower.
-- 2022-09-12 Issue#1: Added fix for PostGIS columns where we do not presume the schema, leave without schema to imply public schema
-- 2022-09-19 Issue#2: Do not add CREATE INDEX statements if the indexes are defined within the Table definition as ADD CONSTRAINT.
-- 2022-12-03 -------- Handle NULL condition for ENUMs
-- 2022-12-07 -------- not setting tablespace correctly for user defined tablespaces
-- 2023-04-12 Issue#6: Handle array types: int, bigint, varchar, even varchars with precisions.
-- 2023-04-13 Issue#7: Incomplete fixing of issue#6
-- 2023-04-21 Issue#8: previously returns actual sequence info (aka \d) instead of serial/bigserial def.
-- 2023-04-21 Issue#10: Consolidated comments into one place under function prototype heading.
-- 2023-05-17 Issue#13: do not specify FKEY for partitions. It is done on the parent and implied on the partitions, else you get "fkey already exists" error
-- 2023-05-20 -------- syntax error, missing THEN keyword
-- 2023-05-20 Issue#11: Handle parent of table being in another schema
-- 2023-07-24 Issue#14: If multiple triggers are defined on a table, show them all not just the first one.
-- 2023-08-03 Issue#15: use utd_schema with USER-DEFINED data types, not defaulting to table schema.
-- 2023-08-03 Issue#16: Make it optional to define the PKEY as external instead of internal.
-- 2023-08-24 Issue#17: Handle case-sensitive tables.
-- 2023-08-26 Issue#17: Had to remove quote_ident when identifying case sensitive tables
-- 2023-08-28 Issue#19: Identified in pull request#18: double-quote reserved keywords
-- 2024-01-25 Issue#20: Handle output for specifying PKEY_EXTERNAL and FKEYS_EXTERNAL options, which misses all other non-primary constraints.
-- 2024-02-18 Issue#22: Handle FKEYS_NONE input option, which was previously ignored.
-- 2024-02-19 Issue#23: Handle complex autogenerated columns. Also append NOT NULL to IDENTITY columns even though technically not necessary.
-- 2024-02-23 Issue#24: Fix empty table problem where we accidentally removed the closing paren thinking a column delimited commas was there...
-- 2024-03-05 Issue#25: Fix case where tablespace def occurs after the WHERE clause of a partial index creation. It must occur BEFORE the WHERE clause.
-- 2024-04-15 Issue#26: Fix case for partition table unique indexes by adding the IF NOT EXISTS phrase, which we already do for non-unique indexes
-- 2024-09-11 Issue#28: Avoid duplication of NOT NULL for identity columns.
-- 2024-09-20 Issue#29: added verbose info for searchpath problems.
-- 2024-10-01 Issue#30: Fixed column def with geometry point defined - geometry geometry(Point, 4326)
-- 2024-11-13 Issue#31: Case-sensitive schemas not handled correctly.
-- 2024-11-20 Issue#32: Show explicit sequence default output, not SERIAL types to emulate the way PG does it. Also use dt2 (formatted), not dt1
-- 2024-11-20 Issue#33: Show partition info for parent table if SHOWPARTS enumeration specified
-- 2024-11-24 Issue#27: V 2.0 NEW Feature: Add owner info if requested through 'OWNER_ACL'
-- 2024-11-25 Issue#35: V 2.0 NEW featrue: Add option for all other ACLs for a table in addition to the owner, option='ALL_ACLS', including policies (row security).
-- 2024-11-26 Issue#36: Fixed issue with PG v9.6 not calling pg_get_coldef() correctly. Also removed attgenerated since not in PG v10 and not used anywhere anyhows
-- 2024-12-15 Issue#37: Fixed issue with case-sensitive user-defined types are not being enclosed with double-quotes.
-- 2024-12-26 --------: Updated License info for GNU
DROP TYPE IF EXISTS public.tabledefs CASCADE;
CREATE TYPE public.tabledefs AS ENUM ('PKEY_INTERNAL','PKEY_EXTERNAL','FKEYS_INTERNAL', 'FKEYS_EXTERNAL', 'COMMENTS', 'FKEYS_NONE', 'INCLUDE_TRIGGERS', 'NO_TRIGGERS', 'SHOWPARTS', 'ACL_OWNER', 'ACL_DCL','ACL_POLICIES');
-- SELECT * FROM public.pg_get_coldef('sample','orders','id');
-- DROP FUNCTION public.pg_get_coldef(text,text,text,boolean);
CREATE OR REPLACE FUNCTION public.pg_get_coldef(
in_schema text,
in_table text,
in_column text,
oldway boolean default False
)
RETURNS text
LANGUAGE plpgsql VOLATILE
AS
$$
DECLARE
v_coldef text;
v_dt1 text;
v_dt2 text;
v_dt3 text;
v_nullable boolean;
v_position int;
v_identity text;
v_hasdflt boolean;
v_dfltexpr text;
BEGIN
IF oldway THEN
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
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;
-- RAISE NOTICE 'DEBUG: oldway=%',v_coldef;
ELSE
-- a.attrelid::regclass::text, a.attname
-- Issue#32: bypass the following query which converts to serial and bypasses explicit sequence defs
-- 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
-- pg_get_expr(ad.adbin, ad.adrelid) = 'nextval(''' || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass || '''::regclass)') THEN CASE a.atttypid
-- 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
-- INTO v_coldef FROM pg_namespace n, pg_class c, pg_attribute a, pg_type t
-- 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;
-- RAISE NOTICE 'DEBUG: newway=%',v_coldef;
-- WHERE n.nspname = 'sequences' AND n.oid = c.relnamespace AND c.relname = 'atable' AND a.attname = 'key' and a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
-- data_type
-- -----------
-- serial
-- WHERE n.nspname = 'sequences' AND n.oid = c.relnamespace AND c.relname = 'vectors3' AND a.attname = 'id' and a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
-- data_type
-- -----------
-- bigint
-- Issue#32: show integer types, not serial types as output
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,
-- Issue#36: removed column attgenerated since we do not use it anywhere and not in PGv10
-- a.attnum, a.attidentity, a.attgenerated, a.atthasdef, pg_get_expr(ad.adbin, ad.adrelid) dfltexpr
-- INTO v_dt1, v_dt2, v_dt3, v_nullable, v_position, v_identity, v_generated, v_hasdflt, v_dfltexpr
a.attnum, a.attidentity, a.atthasdef, pg_get_expr(ad.adbin, ad.adrelid) dfltexpr
INTO v_dt1, v_dt2, v_dt3, v_nullable, v_position, v_identity, v_hasdflt, v_dfltexpr
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)
-- 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;
WHERE c.relkind in ('r','p') AND a.attnum > 0 AND NOT a.attisdropped AND c.relnamespace::regnamespace::text = quote_ident(in_schema) AND c.relname = in_table AND a.attname = in_column;
-- RAISE NOTICE 'schema=% table=% column=% dt1=% dt2=% dt3=% nullable=% pos=% identity=% HasDefault=% DeftExpr=%', in_schema, in_table, in_column, v_dt1,v_dt2,v_dt3,v_nullable,v_position,v_identity,v_hasdflt,v_dfltexpr;
-- WHERE c.relkind in ('r','p') AND a.attnum > 0 AND NOT a.attisdropped AND c.relnamespace::regnamespace::text = 'sequences' AND c.relname = 'atable' AND a.attname = 'key';
-- dt1 | dt2 | dt3 | nullable | attnum | attidentity | attgenerated | atthasdef | dfltexpr
-- ---------+---------+------+----------+--------+-------------+--------------+-----------+-----------------------------------------------------
-- integer | integer | int4 | f | 1 | | | t | nextval('sequences.explicitsequence_key'::regclass)
-- WHERE c.relkind in ('r','p') AND a.attnum > 0 AND NOT a.attisdropped AND c.relnamespace::regnamespace::text = 'sequences' AND c.relname = 'vectors3' AND a.attname = 'id';
-- dt1 | dt2 | dt3 | nullable | attnum | attidentity | attgenerated | atthasdef | dfltexpr
-- --------+--------+------+----------+--------+-------------+--------------+-----------+----------
-- bigint | bigint | int8 | f | 1 | d | | f |
-- Issue#32 handled in calling routine, not here
-- CREATE TABLE atable (key integer NOT NULL default nextval('explicitsequence_key'), avalue text);
-- IF v_dfltexpr IS NULL OR v_dfltexpr = '' THEN
-- v_coldef = v_dt1;
v_coldef = v_dt2;
END IF;
RETURN v_coldef;
END;
$$;
-- SELECT * FROM public.pg_get_tabledef('sample', 'address', false);
DROP FUNCTION IF EXISTS public.pg_get_tabledef(character varying,character varying,boolean,tabledefs[]);
CREATE OR REPLACE FUNCTION public.pg_get_tabledef(
in_schema varchar,
in_table varchar,
_verbose boolean,
VARIADIC arr public.tabledefs[] DEFAULT '{}':: public.tabledefs[]
)
RETURNS text
LANGUAGE plpgsql VOLATILE
AS
$$
DECLARE
v_version text := '2.3 December 26, 2024 GNU General Public License 3.0';
v_schema text := '';
v_coldef text := '';
v_qualified text := '';
v_table_ddl text;
v_table_oid int;
v_colrec record;
v_constraintrec record;
v_trigrec record;
v_indexrec record;
v_rec record;
v_constraint_name text;
v_constraint_def text;
v_pkey_def text := '';
v_fkey_def text := '';
v_fkey_defs text := '';
v_trigger text := '';
v_partition_key text := '';
v_partbound text;
v_parent text;
v_parent_schema text;
v_persist text;
v_seqname text := '';
v_temp text := '';
v_temp2 text;
v_relopts text;
v_tablespace text;
v_pgversion int;
v_context text := '';
bSerial boolean;
bPartition boolean;
bInheritance boolean;
bRelispartition boolean;
constraintarr text[] := '{}';
constraintelement text;
bSkip boolean;
bVerbose boolean := False;
v_cnt1 integer;
v_cnt2 integer;
search_path_old text := '';
search_path_new text := '';
v_partial boolean;
v_pos integer;
v_partinfo text := '';
v_oid oid;
v_partkeydef text := '';
v_owner text := '';
v_acl text := '';
-- assume defaults for ENUMs at the getgo
pkcnt int := 0;
fkcnt int := 0;
trigcnt int := 0;
cmtcnt int := 0;
showpartscnt int := 0;
aclownercnt int := 0;
acldclcnt int := 0;
aclpolicycnt int := 0;
pktype public.tabledefs := 'PKEY_INTERNAL';
fktype public.tabledefs := 'FKEYS_INTERNAL';
trigtype public.tabledefs := 'NO_TRIGGERS';
arglen integer;
vargs text;
avarg public.tabledefs;
-- exception variables
v_ret text;
v_diag1 text;
v_diag2 text;
v_diag3 text;
v_diag4 text;
v_diag5 text;
v_diag6 text;
BEGIN
SET client_min_messages = 'notice';
IF _verbose THEN bVerbose = True; END IF;
SELECT setting from pg_settings where name = 'server_version_num' INTO v_pgversion;
IF bVerbose THEN RAISE NOTICE 'pg_get_tabledef() version=% PG version=%', v_version, v_pgversion; END IF;
-- v17 fix: handle case-sensitive
-- v_qualified = in_schema || '.' || in_table;
arglen := array_length($4, 1);
IF arglen IS NULL THEN
-- nothing to do, so assume defaults
NULL;
ELSE
-- loop thru args
-- IF 'NO_TRIGGERS' = ANY ($4)
-- select array_to_string($4, ',', '***') INTO vargs;
IF bVerbose THEN RAISE NOTICE 'arguments=%', $4; END IF;
FOREACH avarg IN ARRAY $4 LOOP
IF bVerbose THEN RAISE NOTICE 'arg=%', avarg; END IF;
IF avarg = 'FKEYS_INTERNAL' OR avarg = 'FKEYS_EXTERNAL' OR avarg = 'FKEYS_NONE' THEN
fkcnt = fkcnt + 1;
fktype = avarg;
ELSEIF avarg = 'INCLUDE_TRIGGERS' OR avarg = 'NO_TRIGGERS' THEN
trigcnt = trigcnt + 1;
trigtype = avarg;
ELSEIF avarg = 'PKEY_EXTERNAL' THEN
pkcnt = pkcnt + 1;
pktype = avarg;
ELSEIF avarg = 'COMMENTS' THEN
cmtcnt = cmtcnt + 1;
-- Issue#33 check for dups
ELSEIF avarg = 'SHOWPARTS' THEN
showpartscnt = showpartscnt + 1;
-- Issue#27
ELSEIF avarg = 'ACL_OWNER' THEN
aclownercnt = aclownercnt + 1;
-- Issue#35
ELSEIF avarg = 'ACL_DCL' THEN
acldclcnt = acldclcnt + 1;
ELSEIF avarg = 'ACL_POLICIES' THEN
aclpolicycnt = aclpolicycnt + 1;
END IF;
END LOOP;
IF fkcnt > 1 THEN
RAISE WARNING 'Only one foreign key option can be provided. You provided %', fkcnt;
RETURN '';
ELSEIF trigcnt > 1 THEN
RAISE WARNING 'Only one trigger option can be provided. You provided %', trigcnt;
RETURN '';
ELSEIF pkcnt > 1 THEN
RAISE WARNING 'Only one pkey option can be provided. You provided %', pkcnt;
RETURN '';
ELSEIF cmtcnt > 1 THEN
RAISE WARNING 'Only one comments option can be provided. You provided %', cmtcnt;
RETURN '';
ELSEIF showpartscnt > 1 THEN
RAISE WARNING 'Only one SHOWPARTS option can be provided. You provided %', showpartscnt;
RETURN '';
-- Issue#27
ELSEIF aclownercnt > 1 THEN
RAISE WARNING 'Only one ACL_OWNER option can be provided. You provided %', aclownercnt;
RETURN '';
-- Issue#35
ELSEIF acldclcnt > 1 THEN
RAISE WARNING 'Only one ACL_DCL option can be provided. You provided %', acldclcnt;
RETURN '';
ELSEIF aclpolicycnt > 1 THEN
RAISE WARNING 'Only one ACL_POLICIES option can be provided. You provided %', aclpolicycnt;
RETURN '';
END IF;
END IF;
-- Issue#31 - always handle case-sensitive schemas
v_schema = quote_ident(in_schema);
-- RAISE NOTICE 'DEBUG: schema qualified:% before:%', v_schema, in_schema;
-- Issue#27 get owner info too
SELECT c.oid, pg_catalog.pg_get_userbyid(c.relowner) INTO v_table_oid, v_owner FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind in ('r','p') AND c.relname = in_table AND n.nspname = in_schema;
-- set search_path = public before we do anything to force explicit schema qualification but dont forget to set it back before exiting...
SELECT setting INTO search_path_old FROM pg_settings WHERE name = 'search_path';
SELECT REPLACE(REPLACE(setting, '"$user"', '$user'), '$user', '"$user"') INTO search_path_old
FROM pg_settings
WHERE name = 'search_path';
-- RAISE NOTICE 'DEBUG tableddl: saving old search_path: ***%***', search_path_old;
EXECUTE 'SET search_path = "public"';
SELECT setting INTO search_path_new FROM pg_settings WHERE name = 'search_path';
-- RAISE NOTICE 'DEBUG tableddl: using new search path=***%***', search_path_new;
-- throw an error if table was not found
IF (v_table_oid IS NULL) THEN
RAISE EXCEPTION 'schema(%) table(%) does not exist %', v_schema, in_table, v_schema || '.' || in_table;
END IF;
-- get user-defined tablespaces if applicable
SELECT tablespace INTO v_temp FROM pg_tables WHERE schemaname = in_schema and tablename = in_table and tablespace IS NOT NULL;
IF v_temp IS NULL THEN
v_tablespace := 'TABLESPACE pg_default';
ELSE
v_tablespace := 'TABLESPACE ' || v_temp;
END IF;
-- also see if there are any SET commands for this table, ie, autovacuum_enabled=off, fillfactor=70
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)
SELECT string_agg(r.relopts, ', ') as relopts INTO v_temp from relopts r;
IF v_temp IS NULL THEN
v_relopts := '';
ELSE
v_relopts := ' WITH (' || v_temp || ')';
END IF;
-- Issue#27: set owner ACL info
IF aclownercnt = 1 OR acldclcnt = 1 THEN
v_acl = 'ALTER TABLE IF EXISTS ' || quote_ident(in_schema) || '.' || quote_ident(in_table) || ' OWNER TO ' || v_owner || ';' || E'\n' || E'\n';
END IF;
-- Issue#35: add all other ACL info if directed
-- only valid in PG 13 and above
IF acldclcnt = 1 THEN
-- do the revokes first
Select 'REVOKE ALL ON TABLE ' || rtg.table_schema || '.' || rtg.table_name || ' FROM ' || string_agg(distinct rtg.grantee, ',' ORDER BY rtg.grantee) || ';' INTO v_temp
FROM information_schema.role_table_grants rtg, pg_class c, pg_namespace n WHERE n.nspname = quote_ident(in_schema) AND n.oid = c.relnamespace AND c.relkind in ('r','p') AND quote_ident(c.relname) = quote_ident(in_table)
AND n.nspname = rtg.table_schema AND c.relname = rtg.table_name AND pg_catalog.pg_get_userbyid(c.relowner) <> rtg.grantee GROUP BY rtg.table_schema, rtg.table_name ORDER BY 1;
IF v_temp <> '' THEN
v_acl = v_acl || v_temp || E'\n' || E'\n';
END IF;
-- do the grants
FOR v_rec IN
WITH ACLs AS (SELECT rtg.grantee as arole,
CASE WHEN string_agg(rtg.privilege_type, ',' ORDER BY rtg.privilege_type) = 'DELETE,INSERT,REFERENCES,SELECT,TRIGGER,TRUNCATE,UPDATE' THEN 'ALL' ELSE string_agg(rtg.privilege_type, ',' ORDER BY rtg.privilege_type) END as privs
FROM information_schema.role_table_grants rtg, pg_class c, pg_namespace n WHERE n.nspname = quote_ident(in_schema) AND n.oid = c.relnamespace AND c.relkind in ('r','p') AND c.relname = quote_ident(in_table)
AND n.nspname = rtg.table_schema AND c.relname = rtg.table_name AND pg_catalog.pg_get_userbyid(c.relowner) <> rtg.grantee AND rtg.grantor <> rtg.grantee GROUP BY 1 ORDER BY 1)
SELECT 'GRANT ' || acls.privs || ' ON TABLE ' || quote_ident(in_schema) || '.' || quote_ident(in_table) || ' TO ' || acls.arole || ';' as grants FROM ACLs
LOOP
v_acl = v_acl || v_rec.grants || E'\n';
END LOOP;
END IF;
-- Issue#35: RLS/policies only started in PG version 13
IF aclpolicycnt = 1 AND v_pgversion > 130000 THEN
v_acl = v_acl || E'\n';
-- Enable row security if called for
SELECT CASE WHEN p.polpermissive IS TRUE THEN 'true' ELSE 'false' END INTO v_temp
FROM pg_class c, pg_namespace n, pg_policy p WHERE n.nspname = quote_ident(in_schema) AND c.relkind in ('p','r') AND c.relname = quote_ident(in_table) AND c.oid = p.polrelid limit 1;
IF v_temp = 'true' THEN
v_acl = v_acl || 'ALTER TABLE ' || quote_ident(in_schema) || '.' || quote_ident(in_table) || ' ENABLE ROW LEVEL SECURITY;' || E'\n';
END IF;
-- get policies if found
-- For other cases to handle see examples in: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
FOR v_rec IN
SELECT c.oid, n.nspname, c.relname, c.relrowsecurity, p.polname, p.polpermissive, pg_get_expr(p.polqual, p.polrelid) _using, pg_get_expr(p.polwithcheck, p.polrelid) acheck,
CASE WHEN p.polroles = '{0}' THEN '' ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (p.polroles) order by 1),',') END polroles, p.polcmd,
'CREATE POLICY ' || p.polname || ' ON ' || n.nspname || '.' || c.relname || CASE WHEN p.polpermissive THEN ' AS PERMISSIVE ' ELSE ' ' END ||
CASE p.polcmd WHEN 'r' THEN 'FOR SELECT' WHEN 'a' THEN 'FOR SELECT' WHEN 'w' THEN 'FOR UPDATE' WHEN 'd' THEN 'FOR DELETE' ELSE 'FOR ALL' END || ' TO ' ||
CASE WHEN p.polroles = '{0}' THEN 'public' ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (p.polroles) order by 1),',') END ||
CASE WHEN pg_get_expr(p.polqual, p.polrelid) IS NOT NULL THEN ' USING (' || pg_get_expr(p.polqual, p.polrelid) || ')' ELSE '' END ||
CASE WHEN pg_get_expr(p.polwithcheck, p.polrelid) IS NOT NULL THEN ' WITH CHECK (' || pg_get_expr(p.polwithcheck, p.polrelid) || ')' ELSE '' END || ';' as apolicy
FROM pg_class c, pg_namespace n, pg_policy p WHERE n.nspname = quote_ident(in_schema) AND c.relkind in ('p','r') AND c.relname = quote_ident(in_table) AND c.oid = p.polrelid ORDER BY apolicy
LOOP
v_acl = v_acl || v_rec.apolicy || E'\n';
END LOOP;
END IF;
-- -----------------------------------------------------------------------------------
-- Create table defs for partitions/children using inheritance or declarative methods.
-- inheritance: pg_class.relkind = 'r' pg_class.relispartition=false pg_class.relpartbound is NULL
-- declarative: pg_class.relkind = 'r' pg_class.relispartition=true pg_class.relpartbound is NOT NULL
-- -----------------------------------------------------------------------------------
v_partbound := '';
bPartition := False;
bInheritance := False;
IF v_pgversion < 100000 THEN
-- Issue#11: handle parent schema
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
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';
IF (v_parent IS NOT NULL) THEN
bPartition := True;
bInheritance := True;
END IF;
ELSE
-- Issue#11: handle parent schema
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
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';
IF (v_parent IS NOT NULL) THEN
bPartition := True;
IF bRelispartition THEN
bInheritance := False;
ELSE
bInheritance := True;
END IF;
END IF;
END IF;
IF bPartition THEN
--Issue#17 fix for case-sensitive tables
-- 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
-- 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');
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
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');
--Issue#19 put double-quotes around SQL keyword column names
-- Issue#121: fix keyword lookup for table name not column name that does not apply here
-- SELECT COUNT(*) INTO v_cnt2 FROM pg_get_keywords() WHERE word = v_colrec.column_name AND catcode = 'R';
SELECT COUNT(*) INTO v_cnt2 FROM pg_get_keywords() WHERE word = in_table AND catcode = 'R';
IF bInheritance THEN
-- inheritance-based
IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
-- Issue#31 fix
-- v_table_ddl := 'CREATE TABLE ' || in_schema || '."' || in_table || '"( '|| E'\n';
v_table_ddl := 'CREATE TABLE ' || v_schema || '."' || in_table || '"( '|| E'\n';
ELSE
-- Issue#31 fix
-- v_table_ddl := 'CREATE TABLE ' || in_schema || '.' || in_table || '( '|| E'\n';
v_table_ddl := 'CREATE TABLE ' || v_schema || '.' || in_table || '( '|| E'\n';
END IF;
-- Jump to constraints section to add the check constraints
ELSE
-- declarative-based
IF v_relopts <> '' THEN
IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
-- Issue#31 fix
-- v_table_ddl := 'CREATE TABLE ' || in_schema || '."' || in_table || '" PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || v_relopts || ' ' || v_tablespace || '; ' || E'\n';
v_table_ddl := 'CREATE TABLE ' || v_schema || '."' || in_table || '" PARTITION OF ' || v_schema || '.' || v_parent || ' ' || v_partbound || v_relopts || ' ' || v_tablespace || '; ' || E'\n';
ELSE
-- Issue#31 fix
-- v_table_ddl := 'CREATE TABLE ' || in_schema || '.' || in_table || ' PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || v_relopts || ' ' || v_tablespace || '; ' || E'\n';
v_table_ddl := 'CREATE TABLE ' || v_schema || '.' || in_table || ' PARTITION OF ' || v_schema || '.' || v_parent || ' ' || v_partbound || v_relopts || ' ' || v_tablespace || '; ' || E'\n';
END IF;
ELSE
IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
-- Issue#31 fix
-- v_table_ddl := 'CREATE TABLE ' || in_schema || '."' || in_table || '" PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || ' ' || v_tablespace || '; ' || E'\n';
v_table_ddl := 'CREATE TABLE ' || v_schema || '."' || in_table || '" PARTITION OF ' || v_schema || '.' || v_parent || ' ' || v_partbound || ' ' || v_tablespace || '; ' || E'\n';
ELSE
-- Issue#31 fix
-- v_table_ddl := 'CREATE TABLE ' || in_schema || '.' || in_table || ' PARTITION OF ' || in_schema || '.' || v_parent || ' ' || v_partbound || ' ' || v_tablespace || '; ' || E'\n';
v_table_ddl := 'CREATE TABLE ' || v_schema || '.' || in_table || ' PARTITION OF ' || v_schema || '.' || v_parent || ' ' || v_partbound || ' ' || v_tablespace || '; ' || E'\n';
END IF;
END IF;
-- Jump to constraints and index section to add the check constraints and indexes and perhaps FKeys
END IF;
END IF;
IF bVerbose THEN RAISE NOTICE '(1)tabledef so far: %', v_table_ddl; END IF;
IF NOT bPartition THEN
-- see if this is unlogged or temporary table
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';
IF v_persist = 'u' THEN
v_temp := 'UNLOGGED';
ELSIF v_persist = 't' THEN
v_temp := 'TEMPORARY';
ELSE
v_temp := '';
END IF;
END IF;
-- start the create definition for regular tables unless we are in progress creating an inheritance-based child table
IF NOT bPartition THEN
--Issue#17 fix for case-sensitive tables
-- 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
-- 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');
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
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');
IF v_cnt1 > 0 THEN
-- Issue#31 fix
-- v_table_ddl := 'CREATE ' || v_temp || ' TABLE ' || in_schema || '."' || in_table || '" (' || E'\n';
v_table_ddl := 'CREATE ' || v_temp || ' TABLE ' || v_schema || '."' || in_table || '" (' || E'\n';
ELSE
-- Issue#31 fix
-- v_table_ddl := 'CREATE ' || v_temp || ' TABLE ' || in_schema || '.' || in_table || ' (' || E'\n';
v_table_ddl := 'CREATE ' || v_temp || ' TABLE ' || v_schema || '.' || in_table || ' (' || E'\n';
END IF;
END IF;
-- RAISE NOTICE 'DEBUG2: tabledef so far: %', v_table_ddl;
-- define all of the columns in the table unless we are in progress creating an inheritance-based child table
IF NOT bPartition THEN
FOR v_colrec IN
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
FROM information_schema.columns c WHERE (table_schema, table_name) = (in_schema, in_table) ORDER BY ordinal_position
LOOP
-- v17 fix: handle case-sensitive for pg_get_serial_sequence that requires SQL Identifier handling
-- SELECT pg_get_serial_sequence(v_qualified, v_colrec.column_name) into v_temp;
-- v17 fix: handle case-sensitive for pg_get_serial_sequence that requires SQL Identifier handling
-- SELECT CASE WHEN pg_get_serial_sequence(v_qualified, v_colrec.column_name) IS NOT NULL THEN True ELSE False END into bSerial;
SELECT pg_get_serial_sequence(quote_ident(in_schema) || '.' || quote_ident(in_table), v_colrec.column_name) into v_seqname;
IF v_seqname IS NULL THEN v_seqname = ''; END IF;
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;
-- Issue#36: call pg_get_coldef() differently
IF v_pgversion < 100000 THEN
SELECT public.pg_get_coldef(in_schema, in_table,v_colrec.column_name,true) INTO v_coldef;
ELSE
SELECT public.pg_get_coldef(in_schema, in_table,v_colrec.column_name) INTO v_coldef;
END IF;
IF bVerbose THEN
-- RAISE NOTICE '(col loop) coldef=% name=% type=% udt_name=% default=% is_generated=% gen_expr=% Serial=% SeqName=%',
-- v_coldef, v_colrec.column_name, v_colrec.data_type, v_colrec.udt_name, v_colrec.column_default, v_colrec.is_generated, v_colrec.generation_expression, bSerial, v_seqname;
RAISE NOTICE '(col loop) coldef=% name=% type=% udt_name=% default=% is_generated=% gen_expr=% Serial=% SeqName=%',
v_coldef, v_colrec.column_name, v_colrec.data_type, quote_ident(v_colrec.udt_name), v_colrec.column_default, v_colrec.is_generated, v_colrec.generation_expression, bSerial, v_seqname;
END IF;
--Issue#17 put double-quotes around case-sensitive column names
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
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);
--Issue#19 put double-quotes around SQL keyword column names
SELECT COUNT(*) INTO v_cnt2 FROM pg_get_keywords() WHERE word = v_colrec.column_name AND catcode = 'R';
IF v_cnt1 > 0 OR v_cnt2 > 0 THEN
v_table_ddl := v_table_ddl || ' "' || v_colrec.column_name || '" ';
ELSE
v_table_ddl := v_table_ddl || ' ' || v_colrec.column_name || ' ';
END IF;
IF v_colrec.column_default ILIKE 'nextval%' THEN
-- Issue#32: handle explicit sequences for serial types as well simulating pg_dump manner.
v_temp = v_colrec.data_type || ' NOT NULL DEFAULT ' || v_colrec.column_default;
ELSEIF v_colrec.is_generated = 'ALWAYS' and v_colrec.generation_expression IS NOT NULL THEN
-- Issue#23: Handle autogenerated columns and rewrite as a simpler IF THEN ELSE branch instead of a much more complex embedded CASE STATEMENT
-- searchable tsvector GENERATED ALWAYS AS (to_tsvector('simple'::regconfig, COALESCE(translate(email, '@.-'::citext, ' '::text), ''::text)) ) STORED
v_temp = v_colrec.data_type || ' GENERATED ALWAYS AS (' || v_colrec.generation_expression || ') STORED ';
ELSEIF v_colrec.udt_name in ('geometry') THEN
--Issue#30 fix handle geometries separately and use coldef func on it
-- Issue#36: call pg_get_coldef() differently
IF v_pgversion < 100000 THEN
v_temp = public.pg_get_coldef(in_schema, in_table,v_colrec.column_name, true);
ELSE
v_temp = public.pg_get_coldef(in_schema, in_table,v_colrec.column_name);
END IF;
ELSEIF v_colrec.udt_name in ('box2d', 'box2df', 'box3d', 'geography', 'geometry_dump', 'gidx', 'spheroid', 'valid_detail') THEN
v_temp = v_colrec.udt_name;
ELSEIF v_colrec.data_type = 'USER-DEFINED' THEN
-- Issue#31 fix
-- v_temp = v_colrec.udt_schema || '.' || v_colrec.udt_name;
-- Issue#37 handle case-sensitive user-defined types
-- v_temp = quote_ident(v_colrec.udt_schema) || '.' || v_colrec.udt_name;
v_temp = quote_ident(v_colrec.udt_schema) || '.' || quote_ident(v_colrec.udt_name);
ELSEIF v_colrec.data_type = 'ARRAY' THEN
-- Issue#6 fix: handle arrays
-- Issue#36: call pg_get_coldef() differently
IF v_pgversion < 100000 THEN
v_temp = public.pg_get_coldef(in_schema, in_table,v_colrec.column_name, true);
ELSE
v_temp = public.pg_get_coldef(in_schema, in_table,v_colrec.column_name);
END IF;
-- v17 fix: handle case-sensitive for pg_get_serial_sequence that requires SQL Identifier handling
-- WHEN pg_get_serial_sequence(v_qualified, v_colrec.column_name) IS NOT NULL
ELSEIF pg_get_serial_sequence(quote_ident(in_schema) || '.' || quote_ident(in_table), v_colrec.column_name) IS NOT NULL THEN
-- Issue#8 fix: handle serial. Note: NOT NULL is implied so no need to declare it explicitly
-- Issue#36: call pg_get_coldef() differently
IF v_pgversion < 100000 THEN
v_temp = public.pg_get_coldef(in_schema, in_table,v_colrec.column_name, true);
ELSE
v_temp = public.pg_get_coldef(in_schema, in_table,v_colrec.column_name);
END IF;
--ELSEIF (v_colrec.data_type = 'character varying' or v_colrec.udt_name = 'varchar') AND v_colrec.character_maximum_length IS NOT NULL THEN
ELSE
-- Issue#31 fix
-- v_temp = v_colrec.data_type;
v_temp = v_coldef;
END IF;
-- handle IDENTITY columns
IF v_colrec.is_identity = 'YES' THEN
IF v_colrec.identity_generation = 'ALWAYS' THEN
v_temp = v_temp || ' GENERATED ALWAYS AS IDENTITY NOT NULL';
ELSE
v_temp = v_temp || ' GENERATED BY DEFAULT AS IDENTITY NOT NULL';
END IF;
-- Issue#31: no need to add stuff since we get the coldef definition now above
-- ELSEIF v_colrec.character_maximum_length IS NOT NULL THEN
-- v_temp = v_temp || ('(' || v_colrec.character_maximum_length || ')');
-- ELSEIF v_colrec.numeric_precision > 0 AND v_colrec.numeric_scale > 0 THEN
-- v_temp = v_temp || '(' || v_colrec.numeric_precision || ',' || v_colrec.numeric_scale || ')';
END IF;
-- Handle NULL/NOT NULL
IF POSITION('NOT NULL ' IN v_temp) > 0 THEN
-- Issue#32: for explicit sequences with nextval, we already handled NOT NULL, so ignore
NULL;
ELSEIF bSerial AND v_colrec.is_identity = 'NO' THEN
-- Issue#28 - added identity check
v_temp = v_temp || ' NOT NULL';
ELSEIF v_colrec.is_nullable = 'NO' AND v_colrec.is_identity = 'NO' THEN
-- Issue#28 - added identity check
v_temp = v_temp || ' NOT NULL';
ELSEIF v_colrec.is_nullable = 'YES' THEN
v_temp = v_temp || ' NULL';
END IF;
-- Handle defaults
-- Issue#32 fix
-- IF v_colrec.column_default IS NOT null AND NOT bSerial THEN
IF v_colrec.column_default IS NOT null AND NOT bSerial AND v_colrec.column_default NOT ILIKE 'nextval%' THEN
-- RAISE NOTICE 'Setting default for column, %', v_colrec.column_name;
v_temp = v_temp || (' DEFAULT ' || v_colrec.column_default);
END IF;
v_temp = v_temp || ',' || E'\n';
-- RAISE NOTICE 'column def2=%', v_temp;
v_table_ddl := v_table_ddl || v_temp;
-- RAISE NOTICE 'tabledef=%', v_table_ddl;
IF bVerbose THEN RAISE NOTICE 'tabledef: %', v_table_ddl; END IF;
END LOOP;
END IF;
IF bVerbose THEN RAISE NOTICE '(2)tabledef so far: %', v_table_ddl; END IF;
-- define all the constraints: conparentid does not exist pre PGv11
IF v_pgversion < 110000 THEN
FOR v_constraintrec IN
SELECT con.conname as constraint_name, con.contype as constraint_type,
CASE
WHEN con.contype = 'p' THEN 1 -- primary key constraint
WHEN con.contype = 'u' THEN 2 -- unique constraint
WHEN con.contype = 'f' THEN 3 -- foreign key constraint
WHEN con.contype = 'c' THEN 4
ELSE 5
END as type_rank,
pg_get_constraintdef(con.oid) as constraint_definition
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
WHERE nsp.nspname = in_schema AND rel.relname = in_table ORDER BY type_rank
LOOP
v_constraint_name := v_constraintrec.constraint_name;
v_constraint_def := v_constraintrec.constraint_definition;
IF v_constraintrec.type_rank = 1 THEN
IF pkcnt = 0 OR pktype = 'PKEY_INTERNAL' THEN
-- internal def
v_constraint_name := v_constraintrec.constraint_name;
v_constraint_def := v_constraintrec.constraint_definition;
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_name || ' '
|| v_constraint_def
|| ',' || E'\n';
ELSE
-- Issue#16 handle external PG def
SELECT 'ALTER TABLE ONLY ' || in_schema || '.' || c.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_pkey_def
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;
END IF;
IF bPartition THEN
continue;
END IF;
ELSIF v_constraintrec.type_rank = 3 THEN
-- handle foreign key constraints
--Issue#22 fix: added FKEY_NONE check
IF fktype = 'FKEYS_NONE' THEN
-- skip
continue;
ELSIF fkcnt = 0 OR fktype = 'FKEYS_INTERNAL' THEN
-- internal def
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_name || ' '
|| v_constraint_def
|| ',' || E'\n';
ELSE
-- external def
SELECT 'ALTER TABLE ONLY ' || n.nspname || '.' || c2.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_fkey_def
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;
v_fkey_defs = v_fkey_defs || v_fkey_def || E'\n';
END IF;
ELSE
-- handle all other constraints besides PKEY and FKEYS as internal defs by default
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_name || ' '
|| v_constraint_def
|| ',' || E'\n';
END IF;
if bVerbose THEN RAISE NOTICE 'constraint name=% constraint_def=%', v_constraint_name,v_constraint_def; END IF;
constraintarr := constraintarr || v_constraintrec.constraint_name:: text;
END LOOP;
ELSE
-- handle PG versions 11 and up
-- Issue#20: Fix logic for external PKEY and FKEYS
FOR v_constraintrec IN
SELECT con.conname as constraint_name, con.contype as constraint_type,
CASE
WHEN con.contype = 'p' THEN 1 -- primary key constraint
WHEN con.contype = 'u' THEN 2 -- unique constraint
WHEN con.contype = 'f' THEN 3 -- foreign key constraint
WHEN con.contype = 'c' THEN 4
ELSE 5
END as type_rank,
pg_get_constraintdef(con.oid) as constraint_definition
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
WHERE nsp.nspname = in_schema AND rel.relname = in_table
--Issue#13 added this condition:
AND con.conparentid = 0
ORDER BY type_rank
LOOP
v_constraint_name := v_constraintrec.constraint_name;
v_constraint_def := v_constraintrec.constraint_definition;
IF v_constraintrec.type_rank = 1 THEN
IF pkcnt = 0 OR pktype = 'PKEY_INTERNAL' THEN
-- internal def
v_constraint_name := v_constraintrec.constraint_name;
v_constraint_def := v_constraintrec.constraint_definition;
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_name || ' '
|| v_constraint_def
|| ',' || E'\n';
ELSE
-- Issue#16 handle external PG def
SELECT 'ALTER TABLE ONLY ' || in_schema || '.' || c.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_pkey_def
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;
END IF;
IF bPartition THEN
continue;
END IF;
ELSIF v_constraintrec.type_rank = 3 THEN
-- handle foreign key constraints
--Issue#22 fix: added FKEY_NONE check
IF fktype = 'FKEYS_NONE' THEN
-- skip
continue;
ELSIF fkcnt = 0 OR fktype = 'FKEYS_INTERNAL' THEN
-- internal def
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_name || ' '
|| v_constraint_def
|| ',' || E'\n';
ELSE
-- external def
SELECT 'ALTER TABLE ONLY ' || n.nspname || '.' || c2.relname || ' ADD CONSTRAINT ' || r.conname || ' ' || pg_catalog.pg_get_constraintdef(r.oid, true) || ';' INTO v_fkey_def
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
r.conname = v_constraint_name and r.conparentid = 0;
v_fkey_defs = v_fkey_defs || v_fkey_def || E'\n';
END IF;
ELSE
-- handle all other constraints besides PKEY and FKEYS as internal defs by default
v_table_ddl := v_table_ddl || ' ' -- note: two char spacer to start, to indent the column
|| 'CONSTRAINT' || ' '
|| v_constraint_name || ' '
|| v_constraint_def
|| ',' || E'\n';
END IF;
if bVerbose THEN RAISE NOTICE 'constraint name=% constraint_def=%', v_constraint_name,v_constraint_def; END IF;
constraintarr := constraintarr || v_constraintrec.constraint_name:: text;
END LOOP;
END IF;
-- drop the last comma before ending the create statement, which should be right before the carriage return character
-- Issue#24: make sure the comma is there before removing it
select substring(v_table_ddl, length(v_table_ddl) - 1, 1) INTO v_temp;
IF v_temp = ',' THEN
v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';
END IF;
IF bVerbose THEN RAISE NOTICE '(3)tabledef so far: %', trim(v_table_ddl); END IF;
-- ---------------------------------------------------------------------------
-- at this point we have everything up to the last table-enclosing parenthesis
-- ---------------------------------------------------------------------------
IF bVerbose THEN RAISE NOTICE '(4)tabledef so far: %', v_table_ddl; END IF;
-- See if this is an inheritance-based child table and finish up the table create.
IF bPartition and bInheritance THEN
-- Issue#11: handle parent schema
-- v_table_ddl := v_table_ddl || ') INHERITS (' || in_schema || '.' || v_parent || ') ' || E'\n' || v_relopts || ' ' || v_tablespace || ';' || E'\n';
IF v_parent_schema = '' OR v_parent_schema IS NULL THEN v_parent_schema = in_schema; END IF;
v_table_ddl := v_table_ddl || ') INHERITS (' || v_parent_schema || '.' || v_parent || ') ' || E'\n' || v_relopts || ' ' || v_tablespace || ';' || E'\n';
END IF;
IF v_pgversion >= 100000 AND NOT bPartition and NOT bInheritance THEN
-- See if this is a partitioned table (pg_class.relkind = 'p') and add the partitioned key
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)
WHERE n.nspname = in_schema and n.oid = c1.relnamespace and c1.relname = in_table and c1.relkind = 'p';
IF v_partition_key IS NOT NULL AND v_partition_key <> '' THEN
-- add partition clause
-- NOTE: cannot specify default tablespace for partitioned relations
-- v_table_ddl := v_table_ddl || ') PARTITION BY ' || v_partition_key || ' ' || v_tablespace || ';' || E'\n';
v_table_ddl := v_table_ddl || ') PARTITION BY ' || v_partition_key || ';' || E'\n';
ELSEIF v_relopts <> '' THEN
v_table_ddl := v_table_ddl || ') ' || v_relopts || ' ' || v_tablespace || ';' || E'\n';
ELSE
-- end the create definition
v_table_ddl := v_table_ddl || ') ' || v_tablespace || ';' || E'\n';
END IF;
END IF;
IF bVerbose THEN RAISE NOTICE '(5)tabledef so far: %', v_table_ddl; END IF;
-- Add closing paren for regular tables
-- IF NOT bPartition THEN
-- v_table_ddl := v_table_ddl || ') ' || v_relopts || ' ' || v_tablespace || E';\n';
-- END IF;
-- RAISE NOTICE 'ddlsofar3: %', v_table_ddl;
-- Issue#27: add OWNER ACL OR ALL_ACLS info here if directed
IF v_acl <> '' THEN
v_table_ddl := v_table_ddl || v_acl || E'\n';
END IF;
-- Issue#16 create the external PKEY def if indicated
IF v_pkey_def <> '' THEN
v_table_ddl := v_table_ddl || v_pkey_def || E'\n';
END IF;
-- Issue#20
IF v_fkey_defs <> '' THEN
v_table_ddl := v_table_ddl || v_fkey_defs || E'\n';
END IF;
IF bVerbose THEN RAISE NOTICE '(6)tabledef so far: %', v_table_ddl; END IF;
-- create indexes
FOR v_indexrec IN
SELECT indexdef, COALESCE(tablespace, 'pg_default') as tablespace, indexname FROM pg_indexes WHERE (schemaname, tablename) = (in_schema, in_table)
LOOP
-- RAISE NOTICE 'DEBUG6: indexname=% indexdef=%', v_indexrec.indexname, v_indexrec.indexdef;
-- loop through constraints and skip ones already defined
bSkip = False;
FOREACH constraintelement IN ARRAY constraintarr
LOOP
IF constraintelement = v_indexrec.indexname THEN
-- RAISE NOTICE 'DEBUG7: skipping index, %', v_indexrec.indexname;
bSkip = True;
EXIT;
END IF;
END LOOP;
if bSkip THEN CONTINUE; END IF;
-- Add IF NOT EXISTS clause so partition index additions will not be created if declarative partition in effect and index already created on parent
v_indexrec.indexdef := REPLACE(v_indexrec.indexdef, 'CREATE INDEX', 'CREATE INDEX IF NOT EXISTS');
-- Fix Issue#26: do it for unique/primary key indexes as well
v_indexrec.indexdef := REPLACE(v_indexrec.indexdef, 'CREATE UNIQUE INDEX', 'CREATE UNIQUE INDEX IF NOT EXISTS');
-- RAISE NOTICE 'DEBUG8: adding index, %', v_indexrec.indexname;
-- NOTE: cannot specify default tablespace for partitioned relations
IF v_partition_key IS NOT NULL AND v_partition_key <> '' THEN
v_table_ddl := v_table_ddl || v_indexrec.indexdef || ';' || E'\n';
ELSE
-- Issue#25: see if partial index or not
select CASE WHEN i.indpred IS NOT NULL THEN True ELSE False END INTO v_partial
FROM pg_index i JOIN pg_class c1 ON (i.indexrelid = c1.oid) JOIN pg_class c2 ON (i.indrelid = c2.oid)
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;
IF v_partial THEN
-- Put tablespace def before WHERE CLAUSE
v_temp = v_indexrec.indexdef;
v_pos = POSITION(' WHERE ' IN v_temp);
v_temp2 = SUBSTRING(v_temp, v_pos);
v_temp = SUBSTRING(v_temp, 1, v_pos);
v_table_ddl := v_table_ddl || v_temp || ' TABLESPACE ' || v_indexrec.tablespace || v_temp2 || ';' || E'\n';
ELSE
v_table_ddl := v_table_ddl || v_indexrec.indexdef || ' TABLESPACE ' || v_indexrec.tablespace || ';' || E'\n';
END IF;
END IF;
END LOOP;
IF bVerbose THEN RAISE NOTICE '(7)tabledef so far: %', v_table_ddl; END IF;
-- Issue#20: added logic for table and column comments
IF cmtcnt > 0 THEN
FOR v_rec IN
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 '
-- Issue#140
-- 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 || '.' ||
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 || quote_ident(n.nspname) || '.' ||
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
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)
WHERE d.description IS NOT NULL AND n.nspname = in_schema AND c.relname = in_table ORDER BY 2 desc, ddl
LOOP
--RAISE NOTICE 'comments:%', v_rec.ddl;
v_table_ddl = v_table_ddl || v_rec.ddl || E'\n';
END LOOP;
END IF;
IF bVerbose THEN RAISE NOTICE '(8)tabledef so far: %', v_table_ddl; END IF;
IF trigtype = 'INCLUDE_TRIGGERS' THEN
-- Issue#14: handle multiple triggers for a table
FOR v_trigrec IN
select pg_get_triggerdef(t.oid, True) || ';' as triggerdef FROM pg_trigger t, 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' and t.tgrelid = c.oid and NOT t.tgisinternal
LOOP
v_table_ddl := v_table_ddl || v_trigrec.triggerdef;
v_table_ddl := v_table_ddl || E'\n';
IF bVerbose THEN RAISE NOTICE 'triggerdef = %', v_trigrec.triggerdef; END IF;
END LOOP;
END IF;
IF bVerbose THEN RAISE NOTICE '(9)tabledef so far: %', v_table_ddl; END IF;
-- add empty line
v_table_ddl := v_table_ddl || E'\n';
IF bVerbose THEN RAISE NOTICE '(10)tabledef so far: %', v_table_ddl; END IF;
-- Issue#33 implementation follows
IF showpartscnt = 1 THEN
SELECT c.oid, pg_get_partkeydef(c.oid::pg_catalog.oid) INTO v_oid, v_partkeydef FROM pg_class c, pg_namespace n WHERE n.oid = c.relnamespace AND n.nspname = in_schema and c.relname = in_table;
IF v_partkeydef IS NOT NULL THEN
-- v_partinfo := 'Partition key: ' || v_partkeydef || E'\n' || 'Partitions:' || E'\n' ;
v_partinfo := 'Partitions:' || E'\n' ;
FOR v_rec IN
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid = i.inhrelid AND i.inhparent = v_oid
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text
LOOP
v_partinfo := v_partinfo || v_rec.oid || ' ' || v_rec.pg_get_expr || E'\n' ;
END LOOP;
END IF;
END IF;
IF v_partinfo <> '' THEN
v_table_ddl = v_table_ddl || v_partinfo;
END IF;
-- reset search_path back to what it was
-- Issue#29: add verbose info for searchpath stuff
v_context = 'SEARCHPATH';
IF search_path_old = '' THEN
SELECT set_config('search_path', '', false) into v_temp;
IF bVerbose THEN RAISE NOTICE 'SearchPath Cleanup: current searchpath=%', v_temp; END IF;
ELSE
IF bVerbose THEN RAISE NOTICE 'SearchPath Cleanup: resetting searchpath=%', search_path_old; END IF;
EXECUTE 'SET search_path = ' || search_path_old;
END IF;
RETURN v_table_ddl;
EXCEPTION
WHEN others THEN
BEGIN
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;
-- v_ret := 'line=' || v_diag6 || '. '|| v_diag4 || '. ' || v_diag1 || ' .' || v_diag2 || ' .' || v_diag3;
-- put additional coding here if necessary
IF v_context <> '' THEN
v_ret := 'line=' || v_diag6 || '. '|| v_diag4 || '. ' || v_diag1 || ' context=' || v_context;
RAISE WARNING 'Search_path not reset correctly. You may need to adjust it manually. %', v_ret;
ELSE
v_ret := 'line=' || v_diag6 || '. '|| v_diag4 || '. ' || v_diag1;
RAISE EXCEPTION '%', v_ret;
END IF;
RETURN '';
END;