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