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