--
-- Explicit subtransactions (ported from PL/Tcl): subtransaction(callable, ...).
--
CREATE TABLE sx (id int);
-- A successful subtransaction commits its work.
CREATE FUNCTION sx_ok() RETURNS int LANGUAGE plphp AS $$
    subtransaction(function() { spi_exec("insert into sx values (1)"); });
    return 1;
$$;
SELECT sx_ok();
 sx_ok 
-------
     1
(1 row)

SELECT count(*) AS after_ok FROM sx;
 after_ok 
----------
        1
(1 row)

-- A PHP exception thrown in the body rolls the subtransaction back and is
-- catchable by the caller.
CREATE FUNCTION sx_catch() RETURNS text LANGUAGE plphp AS $$
    try {
        subtransaction(function() {
            spi_exec("insert into sx values (2)");
            throw new Exception("rollback me");
        });
    } catch (\Throwable $e) {
        return "caught: " . $e->getMessage();
    }
    return "no exception";
$$;
SELECT sx_catch();
      sx_catch       
---------------------
 caught: rollback me
(1 row)

-- The insert of 2 was rolled back, so the count is unchanged.
SELECT count(*) AS after_catch FROM sx;
 after_catch 
-------------
           1
(1 row)

-- Arguments after the callable are passed through to it, and the callable's
-- return value is returned.
CREATE FUNCTION sx_ret(int) RETURNS int LANGUAGE plphp AS $$
    return subtransaction(function($x) { return $x * 2; }, $args[0]);
$$;
SELECT sx_ret(21);
 sx_ret 
--------
     42
(1 row)

-- Nested subtransactions: the inner one rolls back (its exception is caught)
-- while the outer one commits.
CREATE FUNCTION sx_nested() RETURNS void LANGUAGE plphp AS $$
    subtransaction(function() {
        spi_exec("insert into sx values (10)");
        try {
            subtransaction(function() {
                spi_exec("insert into sx values (11)");
                throw new Exception("inner fails");
            });
        } catch (\Throwable $e) {
            pg_raise('notice', 'inner rolled back: ' . $e->getMessage());
        }
    });
$$;
SELECT sx_nested();
NOTICE:  plphp: inner rolled back: inner fails
 sx_nested 
-----------
 
(1 row)

-- 1 was already present; 10 committed, 11 rolled back.
SELECT id FROM sx ORDER BY id;
 id 
----
  1
 10
(2 rows)

-- A database error inside a subtransaction rolls it back and surfaces as a
-- catchable PgError.
CREATE FUNCTION sx_dberr() RETURNS void LANGUAGE plphp AS $$
    try {
        subtransaction(function() { spi_exec("insert into sx values (1/0)"); });
    } catch (PgError $e) {
        pg_raise('notice', 'caught ' . $e->getSQLState() . ': ' . $e->getMessage());
    }
$$;
SELECT sx_dberr();
NOTICE:  plphp: caught 22012: division by zero
 sx_dberr 
----------
 
(1 row)

SELECT count(*) AS after_dberr FROM sx;
 after_dberr 
-------------
           2
(1 row)

DROP TABLE sx;