-
Notifications
You must be signed in to change notification settings - Fork 115
/
pgoltp.tcl
executable file
·3958 lines (3851 loc) · 199 KB
/
pgoltp.tcl
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
proc build_pgtpcc {} {
global maxvuser suppo ntimes threadscreated _ED
upvar #0 dbdict dbdict
if {[dict exists $dbdict postgresql library ]} {
set library [ dict get $dbdict postgresql library ]
} else { set library "Pgtcl" }
upvar #0 configpostgresql configpostgresql
#set variables to values in dict
setlocaltpccvars $configpostgresql
if {[ tk_messageBox -title "Create Schema" -icon question -message "Ready to create a $pg_count_ware Warehouse PostgreSQL TPROC-C schema\nin host [string toupper $pg_host:$pg_port] sslmode [string toupper $pg_sslmode] under user [ string toupper $pg_user ] in database [ string toupper $pg_dbase ]?" -type yesno ] == yes} {
if { $pg_num_vu eq 1 || $pg_count_ware eq 1 } {
set maxvuser 1
} else {
set maxvuser [ expr $pg_num_vu + 1 ]
}
set suppo 1
set ntimes 1
ed_edit_clear
set _ED(packagekeyname) "TPROC-C creation"
if { [catch {load_virtual} message]} {
puts "Failed to created thread for schema creation: $message"
return
}
.ed_mainFrame.mainwin.textFrame.left.text fastinsert end "#!/usr/local/bin/tclsh8.6
#LOAD LIBRARIES AND MODULES
set library $library
"
.ed_mainFrame.mainwin.textFrame.left.text fastinsert end {if [catch {package require $library} message] { error "Failed to load $library - $message" }
if [catch {::tcl::tm::path add modules} ] { error "Failed to find modules directory" }
if [catch {package require tpcccommon} ] { error "Failed to load tpcc common functions" } else { namespace import tpcccommon::* }
proc CreateStoredProcs { lda ora_compatible citus_compatible pg_storedprocs } {
if { $pg_storedprocs eq "true" } {
puts "CREATING TPCC STORED PROCEDURES"
} else {
puts "CREATING TPCC FUNCTIONS"
}
if { $ora_compatible eq "true" } {
set sql(1) { CREATE OR REPLACE FUNCTION DBMS_RANDOM (INTEGER, INTEGER) RETURNS INTEGER AS $$
DECLARE
start_int ALIAS FOR $1;
end_int ALIAS FOR $2;
BEGIN
RETURN trunc(random() * (end_int-start_int + 1) + start_int);
END;
$$ LANGUAGE 'plpgsql' STRICT;
}
set sql(2) { CREATE OR REPLACE PROCEDURE NEWORD (
no_w_id INTEGER,
no_max_w_id INTEGER,
no_d_id INTEGER,
no_c_id INTEGER,
no_o_ol_cnt INTEGER,
no_c_discount OUT NUMBER,
no_c_last OUT VARCHAR2,
no_c_credit OUT VARCHAR2,
no_d_tax OUT NUMBER,
no_w_tax OUT NUMBER,
no_d_next_o_id IN OUT INTEGER,
tstamp IN DATE )
IS
no_ol_supply_w_id INTEGER;
no_ol_i_id NUMBER;
no_ol_quantity NUMBER;
no_o_all_local INTEGER;
o_id INTEGER;
no_i_name VARCHAR2(24);
no_i_price NUMBER(5,2);
no_i_data VARCHAR2(50);
no_s_quantity NUMBER(6);
no_ol_amount NUMBER(6,2);
no_s_dist_01 CHAR(24);
no_s_dist_02 CHAR(24);
no_s_dist_03 CHAR(24);
no_s_dist_04 CHAR(24);
no_s_dist_05 CHAR(24);
no_s_dist_06 CHAR(24);
no_s_dist_07 CHAR(24);
no_s_dist_08 CHAR(24);
no_s_dist_09 CHAR(24);
no_s_dist_10 CHAR(24);
no_ol_dist_info CHAR(24);
no_s_data VARCHAR2(50);
x NUMBER;
rbk NUMBER;
BEGIN
--assignment below added due to error in appendix code
no_o_all_local := 0;
SELECT c_discount, c_last, c_credit, w_tax
INTO no_c_discount, no_c_last, no_c_credit, no_w_tax
FROM customer, warehouse
WHERE warehouse.w_id = no_w_id AND customer.c_w_id = no_w_id AND
customer.c_d_id = no_d_id AND customer.c_id = no_c_id;
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id - 1, d_tax INTO no_d_next_o_id, no_d_tax;
o_id := no_d_next_o_id;
INSERT INTO ORDERS (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (o_id, no_d_id, no_w_id, no_c_id, tstamp, no_o_ol_cnt, no_o_all_local);
INSERT INTO NEW_ORDER (no_o_id, no_d_id, no_w_id) VALUES (o_id, no_d_id, no_w_id);
--#2.4.1.4
rbk := round(DBMS_RANDOM(1,100));
--#2.4.1.5
FOR loop_counter IN 1 .. no_o_ol_cnt
LOOP
IF ((loop_counter = no_o_ol_cnt) AND (rbk = 1))
THEN
no_ol_i_id := 100001;
ELSE
no_ol_i_id := round(DBMS_RANDOM(1,100000));
END IF;
--#2.4.1.5.2
x := round(DBMS_RANDOM(1,100));
IF ( x > 1 )
THEN
no_ol_supply_w_id := no_w_id;
ELSE
no_ol_supply_w_id := no_w_id;
--no_all_local is actually used before this point so following not beneficial
no_o_all_local := 0;
WHILE ((no_ol_supply_w_id = no_w_id) AND (no_max_w_id != 1))
LOOP
no_ol_supply_w_id := round(DBMS_RANDOM(1,no_max_w_id));
END LOOP;
END IF;
--#2.4.1.5.3
no_ol_quantity := round(DBMS_RANDOM(1,10));
SELECT i_price, i_name, i_data INTO no_i_price, no_i_name, no_i_data
FROM item WHERE i_id = no_ol_i_id;
SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10
INTO no_s_quantity, no_s_data, no_s_dist_01, no_s_dist_02, no_s_dist_03, no_s_dist_04, no_s_dist_05, no_s_dist_06, no_s_dist_07, no_s_dist_08, no_s_dist_09, no_s_dist_10 FROM stock WHERE s_i_id = no_ol_i_id AND s_w_id = no_ol_supply_w_id;
IF ( no_s_quantity > no_ol_quantity )
THEN
no_s_quantity := ( no_s_quantity - no_ol_quantity );
ELSE
no_s_quantity := ( no_s_quantity - no_ol_quantity + 91 );
END IF;
UPDATE stock SET s_quantity = no_s_quantity
WHERE s_i_id = no_ol_i_id
AND s_w_id = no_ol_supply_w_id;
no_ol_amount := ( no_ol_quantity * no_i_price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) );
IF no_d_id = 1
THEN
no_ol_dist_info := no_s_dist_01;
ELSIF no_d_id = 2
THEN
no_ol_dist_info := no_s_dist_02;
ELSIF no_d_id = 3
THEN
no_ol_dist_info := no_s_dist_03;
ELSIF no_d_id = 4
THEN
no_ol_dist_info := no_s_dist_04;
ELSIF no_d_id = 5
THEN
no_ol_dist_info := no_s_dist_05;
ELSIF no_d_id = 6
THEN
no_ol_dist_info := no_s_dist_06;
ELSIF no_d_id = 7
THEN
no_ol_dist_info := no_s_dist_07;
ELSIF no_d_id = 8
THEN
no_ol_dist_info := no_s_dist_08;
ELSIF no_d_id = 9
THEN
no_ol_dist_info := no_s_dist_09;
ELSIF no_d_id = 10
THEN
no_ol_dist_info := no_s_dist_10;
END IF;
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
VALUES (o_id, no_d_id, no_w_id, loop_counter, no_ol_i_id, no_ol_supply_w_id, no_ol_quantity, no_ol_amount, no_ol_dist_info);
END LOOP;
COMMIT;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; }
set sql(3) { CREATE OR REPLACE PROCEDURE DELIVERY (
d_w_id INTEGER,
d_o_carrier_id INTEGER,
tstamp IN DATE )
IS
d_no_o_id INTEGER;
d_d_id INTEGER;
d_c_id NUMBER;
d_ol_total NUMBER;
loop_counter INTEGER;
BEGIN
FOR loop_counter IN 1 .. 10
LOOP
d_d_id := loop_counter;
SELECT no_o_id INTO d_no_o_id FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id ORDER BY no_o_id ASC LIMIT 1;
DELETE FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id AND no_o_id = d_no_o_id;
SELECT o_c_id INTO d_c_id FROM orders
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE orders SET o_carrier_id = d_o_carrier_id
WHERE o_id = d_no_o_id AND o_d_id = d_d_id AND
o_w_id = d_w_id;
UPDATE order_line SET ol_delivery_d = tstamp
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id AND
ol_w_id = d_w_id;
SELECT SUM(ol_amount) INTO d_ol_total
FROM order_line
WHERE ol_o_id = d_no_o_id AND ol_d_id = d_d_id
AND ol_w_id = d_w_id;
UPDATE customer SET c_balance = c_balance + d_ol_total
WHERE c_id = d_c_id AND c_d_id = d_d_id AND
c_w_id = d_w_id;
DBMS_OUTPUT.PUT_LINE('D: ' || d_d_id || 'O: ' || d_no_o_id || 'time ' || tstamp);
END LOOP;
COMMIT;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; }
set sql(4) { CREATE OR REPLACE PROCEDURE PAYMENT (
p_w_id INTEGER,
p_d_id INTEGER,
p_c_w_id INTEGER,
p_c_d_id INTEGER,
p_c_id IN OUT NUMBER(5,0),
byname INTEGER,
p_h_amount NUMBER,
p_c_last IN OUT VARCHAR2(16),
p_w_street_1 OUT VARCHAR2(20),
p_w_street_2 OUT VARCHAR2(20),
p_w_city OUT VARCHAR2(20),
p_w_state OUT CHAR(2),
p_w_zip OUT CHAR(9),
p_d_street_1 OUT VARCHAR2(20),
p_d_street_2 OUT VARCHAR2(20),
p_d_city OUT VARCHAR2(20),
p_d_state OUT CHAR(2),
p_d_zip OUT CHAR(9),
p_c_first OUT VARCHAR2(16),
p_c_middle OUT CHAR(2),
p_c_street_1 OUT VARCHAR2(20),
p_c_street_2 OUT VARCHAR2(20),
p_c_city OUT VARCHAR2(20),
p_c_state OUT CHAR(2),
p_c_zip OUT CHAR(9),
p_c_phone OUT CHAR(16),
p_c_since OUT DATE,
p_c_credit IN OUT CHAR(2),
p_c_credit_lim OUT NUMBER(12, 2),
p_c_discount OUT NUMBER(4, 4),
p_c_balance IN OUT NUMBER(12, 2),
p_c_data OUT VARCHAR2(500),
tstamp IN DATE )
IS
namecnt INTEGER;
p_d_name VARCHAR2(11);
p_w_name VARCHAR2(11);
p_c_new_data VARCHAR2(500);
h_data VARCHAR2(30);
CURSOR c_byname IS
SELECT c_first, c_middle, c_id,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_last = p_c_last
ORDER BY c_first;
BEGIN
UPDATE warehouse SET w_ytd = w_ytd + p_h_amount
WHERE w_id = p_w_id;
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name
INTO p_w_street_1, p_w_street_2, p_w_city, p_w_state, p_w_zip, p_w_name
FROM warehouse
WHERE w_id = p_w_id;
UPDATE district SET d_ytd = d_ytd + p_h_amount
WHERE d_w_id = p_w_id AND d_id = p_d_id;
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name
INTO p_d_street_1, p_d_street_2, p_d_city, p_d_state, p_d_zip, p_d_name
FROM district
WHERE d_w_id = p_w_id AND d_id = p_d_id;
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = p_c_last AND c_d_id = p_c_d_id AND c_w_id = p_c_w_id;
OPEN c_byname;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
FOR loop_counter IN 0 .. cast((namecnt/2) AS INTEGER)
LOOP
FETCH c_byname
INTO p_c_first, p_c_middle, p_c_id, p_c_street_1, p_c_street_2, p_c_city,
p_c_state, p_c_zip, p_c_phone, p_c_credit, p_c_credit_lim, p_c_discount, p_c_balance, p_c_since;
END LOOP;
CLOSE c_byname;
ELSE
SELECT c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
INTO p_c_first, p_c_middle, p_c_last,
p_c_street_1, p_c_street_2, p_c_city, p_c_state, p_c_zip,
p_c_phone, p_c_credit, p_c_credit_lim,
p_c_discount, p_c_balance, p_c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
END IF;
p_c_balance := ( p_c_balance + p_h_amount );
IF p_c_credit = 'BC'
THEN
SELECT c_data INTO p_c_data
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
-- The following statement in the TPC-C specification appendix is incorrect
-- copied setting of h_data from later on in the procedure to here as well
h_data := ( p_w_name || ' ' || p_d_name );
p_c_new_data := (TO_CHAR(p_c_id) || ' ' || TO_CHAR(p_c_d_id) || ' ' ||
TO_CHAR(p_c_w_id) || ' ' || TO_CHAR(p_d_id) || ' ' || TO_CHAR(p_w_id) || ' ' || TO_CHAR(p_h_amount,'9999.99') || TO_CHAR(tstamp) || h_data);
p_c_new_data := substr(CONCAT(p_c_new_data,p_c_data),1,500-(LENGTH(p_c_new_data)));
UPDATE customer
SET c_balance = p_c_balance, c_data = p_c_new_data
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND
c_id = p_c_id;
ELSE
UPDATE customer SET c_balance = p_c_balance
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND
c_id = p_c_id;
END IF;
--setting of h_data is here in the TPC-C appendix
h_data := ( p_w_name|| ' ' || p_d_name );
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id,
h_w_id, h_date, h_amount, h_data)
VALUES (p_c_d_id, p_c_w_id, p_c_id, p_d_id,
p_w_id, tstamp, p_h_amount, h_data);
COMMIT;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; }
set sql(5) { CREATE OR REPLACE PROCEDURE OSTAT (
os_w_id INTEGER,
os_d_id INTEGER,
os_c_id IN OUT INTEGER,
byname INTEGER,
os_c_last IN OUT VARCHAR2,
os_c_first OUT VARCHAR2,
os_c_middle OUT VARCHAR2,
os_c_balance OUT NUMBER,
os_o_id OUT INTEGER,
os_entdate OUT DATE,
os_o_carrier_id OUT INTEGER )
IS
TYPE numbertable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
os_ol_i_id numbertable;
os_ol_supply_w_id numbertable;
os_ol_quantity numbertable;
TYPE amounttable IS TABLE OF NUMBER(6,2) INDEX BY BINARY_INTEGER;
os_ol_amount amounttable;
TYPE datetable IS TABLE OF DATE INDEX BY BINARY_INTEGER;
os_ol_delivery_d datetable;
namecnt INTEGER;
i BINARY_INTEGER;
CURSOR c_name IS
SELECT c_balance, c_first, c_middle, c_id
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id
ORDER BY c_first;
CURSOR c_line IS
SELECT ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount, ol_delivery_d
FROM order_line
WHERE ol_o_id = os_o_id AND ol_d_id = os_d_id AND ol_w_id = os_w_id;
os_c_line c_line%ROWTYPE;
BEGIN
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
OPEN c_name;
FOR loop_counter IN 0 .. cast((namecnt/2) AS INTEGER)
LOOP
FETCH c_name
INTO os_c_balance, os_c_first, os_c_middle, os_c_id;
END LOOP;
close c_name;
ELSE
SELECT c_balance, c_first, c_middle, c_last
INTO os_c_balance, os_c_first, os_c_middle, os_c_last
FROM customer
WHERE c_id = os_c_id AND c_d_id = os_d_id AND c_w_id = os_w_id;
END IF;
SELECT o_id, o_carrier_id, o_entry_d
INTO os_o_id, os_o_carrier_id, os_entdate
FROM
(SELECT o_id, o_carrier_id, o_entry_d
FROM orders where o_d_id = os_d_id AND o_w_id = os_w_id and o_c_id=os_c_id
ORDER BY o_id DESC)
WHERE ROWNUM = 1;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found THEN
dbms_output.put_line('No orders for customer');
ROLLBACK;
WHEN OTHERS THEN
i := 0;
FOR os_c_line IN c_line
LOOP
os_ol_i_id(i) := os_c_line.ol_i_id;
os_ol_supply_w_id(i) := os_c_line.ol_supply_w_id;
os_ol_quantity(i) := os_c_line.ol_quantity;
os_ol_amount(i) := os_c_line.ol_amount;
os_ol_delivery_d(i) := os_c_line.ol_delivery_d;
i := i+1;
END LOOP;
COMMIT;
END; }
set sql(6) { CREATE OR REPLACE PROCEDURE SLEV (
st_w_id INTEGER,
st_d_id INTEGER,
threshold INTEGER,
stock_count OUT INTEGER )
IS
st_o_id NUMBER;
BEGIN
SELECT d_next_o_id INTO st_o_id
FROM district
WHERE d_w_id=st_w_id AND d_id=st_d_id;
SELECT COUNT(DISTINCT (s_i_id)) INTO stock_count
FROM order_line, stock
WHERE ol_w_id = st_w_id AND
ol_d_id = st_d_id AND (ol_o_id < st_o_id) AND
ol_o_id >= (st_o_id - 20) AND s_w_id = st_w_id AND
s_i_id = ol_i_id AND s_quantity < threshold;
COMMIT;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END; }
if { $citus_compatible eq "true" } {
set sql(7) { SELECT create_distributed_function('dbms_random(int,int)') }
set sql(8) { SELECT create_distributed_function(oid, '$1', colocate_with:='warehouse') FROM pg_catalog.pg_proc WHERE proname IN ('neword', 'delivery', 'payment', 'ostat', 'slev') }
}
for { set i 1 } { $i <= [array size sql] } { incr i } {
set result [ pg_exec $lda $sql($i) ]
if {[pg_result $result -status] ni {"PGRES_TUPLES_OK" "PGRES_COMMAND_OK"}} {
error "[pg_result $result -error]"
} else {
pg_result $result -clear
}
}
} else {
if { $pg_storedprocs eq "true" } {
set sql(1) { CREATE OR REPLACE FUNCTION DBMS_RANDOM (INTEGER, INTEGER) RETURNS INTEGER AS $$
DECLARE
start_int ALIAS FOR $1;
end_int ALIAS FOR $2;
BEGIN
RETURN trunc(random() * (end_int-start_int + 1) + start_int);
END;
$$ LANGUAGE 'plpgsql' STRICT;
}
set sql(2) {CREATE OR REPLACE PROCEDURE NEWORD (
no_w_id IN INTEGER,
no_max_w_id IN INTEGER,
no_d_id IN INTEGER,
no_c_id IN INTEGER,
no_o_ol_cnt IN INTEGER,
no_c_discount INOUT NUMERIC,
no_c_last INOUT VARCHAR,
no_c_credit INOUT VARCHAR,
no_d_tax INOUT NUMERIC,
no_w_tax INOUT NUMERIC,
no_d_next_o_id INOUT INTEGER,
tstamp IN TIMESTAMP )
AS $$
DECLARE
no_s_quantity NUMERIC;
no_o_all_local SMALLINT;
rbk SMALLINT;
item_id_array INT[];
supply_wid_array INT[];
quantity_array SMALLINT[];
order_line_array SMALLINT[];
stock_dist_array CHAR(24)[];
s_quantity_array SMALLINT[];
price_array NUMERIC(5,2)[];
amount_array NUMERIC(5,2)[];
BEGIN
no_o_all_local := 1;
SELECT c_discount, c_last, c_credit, w_tax
INTO no_c_discount, no_c_last, no_c_credit, no_w_tax
FROM customer, warehouse
WHERE warehouse.w_id = no_w_id AND customer.c_w_id = no_w_id AND customer.c_d_id = no_d_id AND customer.c_id = no_c_id;
--#2.4.1.4
rbk := round(DBMS_RANDOM(1,100));
--#2.4.1.5
FOR loop_counter IN 1 .. no_o_ol_cnt
LOOP
IF ((loop_counter = no_o_ol_cnt) AND (rbk = 1))
THEN
item_id_array[loop_counter] := 100001;
ELSE
item_id_array[loop_counter] := round(DBMS_RANDOM(1,100000));
END IF;
--#2.4.1.5.2
IF ( round(DBMS_RANDOM(1,100)) > 1 )
THEN
supply_wid_array[loop_counter] := no_w_id;
ELSE
no_o_all_local := 0;
supply_wid_array[loop_counter] := 1 + MOD(CAST (no_w_id + round(DBMS_RANDOM(0,no_max_w_id-1)) AS INT), no_max_w_id);
END IF;
--#2.4.1.5.3
quantity_array[loop_counter] := round(DBMS_RANDOM(1,10));
order_line_array[loop_counter] := loop_counter;
END LOOP;
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id - 1, d_tax INTO no_d_next_o_id, no_d_tax;
INSERT INTO ORDERS (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (no_d_next_o_id, no_d_id, no_w_id, no_c_id, current_timestamp, no_o_ol_cnt, no_o_all_local);
INSERT INTO NEW_ORDER (no_o_id, no_d_id, no_w_id) VALUES (no_d_next_o_id, no_d_id, no_w_id);
SELECT array_agg ( i_price )
INTO price_array
FROM UNNEST(item_id_array) item_id
LEFT JOIN item ON i_id = item_id;
IF no_d_id = 1
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_01 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 2
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_02 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 3
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_03 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 4
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_04 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 5
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_05 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 6
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_06 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 7
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_07 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 8
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_08 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 9
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_09 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
ELSIF no_d_id = 10
THEN
WITH stock_update AS (
UPDATE stock
SET s_quantity = ( CASE WHEN s_quantity < (item_stock.quantity + 10) THEN s_quantity + 91 ELSE s_quantity END) - item_stock.quantity
FROM UNNEST(item_id_array, supply_wid_array, quantity_array, price_array)
AS item_stock (item_id, supply_wid, quantity, price)
WHERE stock.s_i_id = item_stock.item_id
AND stock.s_w_id = item_stock.supply_wid
AND stock.s_w_id = ANY(supply_wid_array)
RETURNING stock.s_dist_10 as s_dist, stock.s_quantity, ( item_stock.quantity + item_stock.price * ( 1 + no_w_tax + no_d_tax ) * ( 1 - no_c_discount ) ) amount
)
SELECT array_agg ( s_dist ), array_agg ( s_quantity ), array_agg ( amount )
FROM stock_update
INTO stock_dist_array, s_quantity_array, amount_array;
END IF;
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
SELECT no_d_next_o_id,
no_d_id,
no_w_id,
data.line_number,
data.item_id,
data.supply_wid,
data.quantity,
data.amount,
data.stock_dist
FROM UNNEST(order_line_array,
item_id_array,
supply_wid_array,
quantity_array,
amount_array,
stock_dist_array)
AS data( line_number, item_id, supply_wid, quantity, amount, stock_dist);
no_s_quantity := 0;
FOR loop_counter IN 1 .. no_o_ol_cnt
LOOP
no_s_quantity := no_s_quantity + CAST( amount_array[loop_counter] AS NUMERIC);
END LOOP;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END;
$$
LANGUAGE 'plpgsql';}
set sql(3) {CREATE OR REPLACE PROCEDURE DELIVERY (
d_w_id IN INTEGER,
d_o_carrier_id IN INTEGER,
tstamp IN TIMESTAMP )
AS $$
DECLARE
loop_counter SMALLINT;
d_id_in_array SMALLINT[] := ARRAY[1,2,3,4,5,6,7,8,9,10];
d_id_array SMALLINT[];
o_id_array INT[];
c_id_array INT[];
order_count SMALLINT;
sum_amounts NUMERIC[];
customer_count INT;
BEGIN
WITH new_order_delete AS (
DELETE
FROM new_order as del_new_order
USING UNNEST(d_id_in_array) AS d_ids
WHERE no_d_id = d_ids
AND no_w_id = d_w_id
AND del_new_order.no_o_id = (select min (select_new_order.no_o_id)
from new_order as select_new_order
where no_d_id = d_ids
and no_w_id = d_w_id)
RETURNING del_new_order.no_o_id, del_new_order.no_d_id
)
SELECT array_agg(no_o_id), array_agg(no_d_id)
FROM new_order_delete
INTO o_id_array, d_id_array;
UPDATE orders
SET o_carrier_id = d_o_carrier_id
FROM UNNEST(o_id_array, d_id_array) AS ids(o_id, d_id)
WHERE orders.o_id = ids.o_id
AND o_d_id = ids.d_id
AND o_w_id = d_w_id;
WITH order_line_update AS (
UPDATE order_line
SET ol_delivery_d = current_timestamp
FROM UNNEST(o_id_array, d_id_array) AS ids(o_id, d_id)
WHERE ol_o_id = ids.o_id
AND ol_d_id = ids.d_id
AND ol_w_id = d_w_id
RETURNING ol_d_id, ol_o_id, ol_amount
)
SELECT array_agg(ol_d_id), array_agg(c_id), array_agg(sum_amount)
FROM ( SELECT ol_d_id,
( SELECT DISTINCT o_c_id FROM orders WHERE o_id = ol_o_id AND o_d_id = ol_d_id AND o_w_id = d_w_id) AS c_id,
sum(ol_amount) AS sum_amount
FROM order_line_update
GROUP BY ol_d_id, ol_o_id ) AS inner_sum
INTO d_id_array, c_id_array, sum_amounts;
UPDATE customer
SET c_balance = COALESCE(c_balance,0) + ids_and_sums.sum_amounts
FROM UNNEST(d_id_array, c_id_array, sum_amounts) AS ids_and_sums(d_id, c_id, sum_amounts)
WHERE customer.c_id = ids_and_sums.c_id
AND c_d_id = ids_and_sums.d_id
AND c_w_id = d_w_id;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END;
$$
LANGUAGE 'plpgsql';}
set sql(4) {CREATE OR REPLACE PROCEDURE PAYMENT (
p_w_id IN INTEGER,
p_d_id IN INTEGER,
p_c_w_id IN INTEGER,
p_c_d_id IN INTEGER,
byname IN INTEGER,
p_h_amount IN NUMERIC,
p_c_credit INOUT CHAR(2),
p_c_last INOUT VARCHAR(16),
p_c_id INOUT INTEGER,
p_w_street_1 INOUT VARCHAR(20),
p_w_street_2 INOUT VARCHAR(20),
p_w_city INOUT VARCHAR(20),
p_w_state INOUT CHAR(2),
p_w_zip INOUT CHAR(9),
p_d_street_1 INOUT VARCHAR(20),
p_d_street_2 INOUT VARCHAR(20),
p_d_city INOUT VARCHAR(20),
p_d_state INOUT CHAR(2),
p_d_zip INOUT CHAR(9),
p_c_first INOUT VARCHAR(16),
p_c_middle INOUT CHAR(2),
p_c_street_1 INOUT VARCHAR(20),
p_c_street_2 INOUT VARCHAR(20),
p_c_city INOUT VARCHAR(20),
p_c_state INOUT CHAR(2),
p_c_zip INOUT CHAR(9),
p_c_phone INOUT CHAR(16),
p_c_since INOUT TIMESTAMP,
p_c_credit_lim INOUT NUMERIC(12,2),
p_c_discount INOUT NUMERIC(4,4),
p_c_balance INOUT NUMERIC(12,2),
p_c_data INOUT VARCHAR(500),
tstamp IN TIMESTAMP)
AS $$
DECLARE
name_count SMALLINT;
p_d_name VARCHAR(11);
p_w_name VARCHAR(11);
h_data VARCHAR(30);
c_byname CURSOR FOR
SELECT c_first, c_middle, c_id,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_last = p_c_last
ORDER BY c_first;
BEGIN
UPDATE warehouse
SET w_ytd = w_ytd + p_h_amount
WHERE w_id = p_w_id
RETURNING w_street_1, w_street_2, w_city, w_state, w_zip, w_name
INTO p_w_street_1, p_w_street_2, p_w_city, p_w_state, p_w_zip, p_w_name;
UPDATE district
SET d_ytd = d_ytd + p_h_amount
WHERE d_w_id = p_w_id AND d_id = p_d_id
RETURNING d_street_1, d_street_2, d_city, d_state, d_zip, d_name
INTO p_d_street_1, p_d_street_2, p_d_city, p_d_state, p_d_zip, p_d_name;
IF ( byname = 1 )
THEN
SELECT count(c_last) INTO name_count
FROM customer
WHERE c_last = p_c_last AND c_d_id = p_c_d_id AND c_w_id = p_c_w_id;
OPEN c_byname;
FOR loop_counter IN 1 .. cast( name_count/2 AS INT)
LOOP
FETCH c_byname
INTO p_c_first, p_c_middle, p_c_id, p_c_street_1, p_c_street_2, p_c_city, p_c_state, p_c_zip, p_c_phone, p_c_credit, p_c_credit_lim, p_c_discount, p_c_balance, p_c_since;
END LOOP;
CLOSE c_byname;
ELSE
SELECT c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
INTO p_c_first, p_c_middle, p_c_last,
p_c_street_1, p_c_street_2, p_c_city, p_c_state, p_c_zip,
p_c_phone, p_c_credit, p_c_credit_lim,
p_c_discount, p_c_balance, p_c_since
FROM customer
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id;
END IF;
h_data := p_w_name || ' ' || p_d_name;
IF p_c_credit = 'BC'
THEN
UPDATE customer
SET c_balance = p_c_balance - p_h_amount,
c_data = substr ((p_c_id || ' ' ||
p_c_d_id || ' ' ||
p_c_w_id || ' ' ||
p_d_id || ' ' ||
p_w_id || ' ' ||
to_char (p_h_amount, '9999.99') || ' ' ||
TO_CHAR(tstamp,'YYYYMMDDHH24MISS') || ' ' ||
h_data || ' | ') || c_data, 1, 500)
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id
RETURNING c_balance, c_data INTO p_c_balance, p_c_data;
ELSE
UPDATE customer
SET c_balance = p_c_balance - p_h_amount
WHERE c_w_id = p_c_w_id AND c_d_id = p_c_d_id AND c_id = p_c_id
RETURNING c_balance, c_data INTO p_c_balance, p_c_data;
END IF;
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id,h_w_id, h_date, h_amount, h_data)
VALUES (p_c_d_id, p_c_w_id, p_c_id, p_d_id, p_w_id, tstamp, p_h_amount, h_data);
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END;
$$
LANGUAGE 'plpgsql';}
set sql(5) {CREATE OR REPLACE PROCEDURE OSTAT (
os_w_id IN INTEGER,
os_d_id IN INTEGER,
os_c_id INOUT INTEGER,
byname IN INTEGER,
os_c_last INOUT VARCHAR,
os_c_first INOUT VARCHAR,
os_c_middle INOUT VARCHAR,
os_c_balance INOUT NUMERIC,
os_o_id INOUT INTEGER,
os_entdate INOUT TIMESTAMP,
os_o_carrier_id INOUT INTEGER,
os_c_line INOUT TEXT DEFAULT '')
AS $$
DECLARE
out_os_c_id INTEGER;
out_os_c_last VARCHAR;
os_ol RECORD;
namecnt INTEGER;
c_name CURSOR FOR
SELECT c_balance, c_first, c_middle, c_id
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id
ORDER BY c_first;
BEGIN
IF ( byname = 1 )
THEN
SELECT count(c_id) INTO namecnt
FROM customer
WHERE c_last = os_c_last AND c_d_id = os_d_id AND c_w_id = os_w_id;
IF ( MOD (namecnt, 2) = 1 )
THEN
namecnt := (namecnt + 1);
END IF;
OPEN c_name;
FOR loop_counter IN 0 .. cast((namecnt/2) AS INTEGER)
LOOP
FETCH c_name
INTO os_c_balance, os_c_first, os_c_middle, os_c_id;
END LOOP;
CLOSE c_name;
ELSE
SELECT c_balance, c_first, c_middle, c_last
INTO os_c_balance, os_c_first, os_c_middle, os_c_last
FROM customer
WHERE c_id = os_c_id AND c_d_id = os_d_id AND c_w_id = os_w_id;
END IF;
SELECT o_id, o_carrier_id, o_entry_d
INTO os_o_id, os_o_carrier_id, os_entdate
FROM (SELECT o_id, o_carrier_id, o_entry_d
FROM orders where o_d_id = os_d_id AND o_w_id = os_w_id and o_c_id=os_c_id
ORDER BY o_id DESC) AS SUBQUERY
LIMIT 1;
FOR os_ol IN
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d, out_os_c_id, out_os_c_last, os_c_first, os_c_middle, os_c_balance, os_o_id, os_entdate, os_o_carrier_id
FROM order_line
WHERE ol_o_id = os_o_id AND ol_d_id = os_d_id AND ol_w_id = os_w_id
LOOP
os_c_line := os_c_line || ',' || os_ol.ol_i_id || ',' || os_ol.ol_supply_w_id || ',' || os_ol.ol_quantity || ',' || os_ol.ol_amount || ',' || os_ol.ol_delivery_d;
END LOOP;
EXCEPTION
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;
END;
$$
LANGUAGE 'plpgsql';}
set sql(6) {CREATE OR REPLACE PROCEDURE SLEV (
st_w_id IN INTEGER,
st_d_id IN INTEGER,
threshold IN INTEGER,
stock_count INOUT INTEGER )
AS $$
BEGIN
SELECT COUNT(DISTINCT (s_i_id)) INTO stock_count
FROM order_line, stock, district
WHERE ol_w_id = st_w_id
AND ol_d_id = st_d_id
AND d_w_id=st_w_id
AND d_id=st_d_id
AND (ol_o_id < d_next_o_id)