1-- suppress CONTEXT so that function OIDs aren't in output
2\set VERBOSITY terse
3
4CREATE TABLE test1 (a int, b text);
5
6
7CREATE PROCEDURE transaction_test1()
8LANGUAGE pltcl
9AS $$
10for {set i 0} {$i < 10} {incr i} {
11    spi_exec "INSERT INTO test1 (a) VALUES ($i)"
12    if {$i % 2 == 0} {
13        commit
14    } else {
15        rollback
16    }
17}
18$$;
19
20CALL transaction_test1();
21
22SELECT * FROM test1;
23
24
25TRUNCATE test1;
26
27-- not allowed in a function
28CREATE FUNCTION transaction_test2() RETURNS int
29LANGUAGE pltcl
30AS $$
31for {set i 0} {$i < 10} {incr i} {
32    spi_exec "INSERT INTO test1 (a) VALUES ($i)"
33    if {$i % 2 == 0} {
34        commit
35    } else {
36        rollback
37    }
38}
39return 1
40$$;
41
42SELECT transaction_test2();
43
44SELECT * FROM test1;
45
46
47-- also not allowed if procedure is called from a function
48CREATE FUNCTION transaction_test3() RETURNS int
49LANGUAGE pltcl
50AS $$
51spi_exec "CALL transaction_test1()"
52return 1
53$$;
54
55SELECT transaction_test3();
56
57SELECT * FROM test1;
58
59
60-- commit inside cursor loop
61CREATE TABLE test2 (x int);
62INSERT INTO test2 VALUES (0), (1), (2), (3), (4);
63
64TRUNCATE test1;
65
66CREATE PROCEDURE transaction_test4a()
67LANGUAGE pltcl
68AS $$
69spi_exec -array row "SELECT * FROM test2 ORDER BY x" {
70    spi_exec "INSERT INTO test1 (a) VALUES ($row(x))"
71    commit
72}
73$$;
74
75CALL transaction_test4a();
76
77SELECT * FROM test1;
78
79
80-- rollback inside cursor loop
81TRUNCATE test1;
82
83CREATE PROCEDURE transaction_test4b()
84LANGUAGE pltcl
85AS $$
86spi_exec -array row "SELECT * FROM test2 ORDER BY x" {
87    spi_exec "INSERT INTO test1 (a) VALUES ($row(x))"
88    rollback
89}
90$$;
91
92CALL transaction_test4b();
93
94SELECT * FROM test1;
95
96
97DROP TABLE test1;
98DROP TABLE test2;
99