-- Catchable database errors: every database error (and pg_raise/elog ERROR)
-- is thrown as a PgError exception, like PL/Perl's eval-trappable errors and
-- PL/Tcl's catch, carrying SQLSTATE, detail, and hint.
-- A failed query can be caught, with its SQLSTATE
CREATE FUNCTION err_catch() RETURNS text LANGUAGE plphp AS $$
	try {
		spi_exec("select 1/0");
		return "not reached";
	} catch (PgError $e) {
		return "caught [" . $e->getSQLState() . "] " . $e->getMessage();
	}
$$;
SELECT err_catch();
            err_catch            
---------------------------------
 caught [22012] division by zero
(1 row)

-- After catching, the function continues normally (the failed call's
-- subtransaction was already rolled back)
CREATE FUNCTION err_retry() RETURNS int LANGUAGE plphp AS $$
	$n = 0;
	foreach (array("select 1/0 as x", "select 41+1 as x") as $q) {
		try {
			$r = spi_exec($q);
			$row = spi_fetch_row($r);
			$n = $row['x'];
		} catch (PgError $e) {
			pg_raise('notice', 'skipped: ' . $e->getMessage());
		}
	}
	return $n;
$$;
SELECT err_retry();
NOTICE:  plphp: skipped: division by zero
 err_retry 
-----------
        42
(1 row)

-- detail is carried over
CREATE TABLE errt (a int PRIMARY KEY);
INSERT INTO errt VALUES (1);
CREATE FUNCTION err_detail() RETURNS text LANGUAGE plphp AS $$
	try {
		spi_exec("insert into errt values (1)");
	} catch (PgError $e) {
		return $e->getSQLState() . " / " . $e->getDetail();
	}
	return "not reached";
$$;
SELECT err_detail();
             err_detail              
-------------------------------------
 23505 / Key (a)=(1) already exists.
(1 row)

-- pg_raise('error') and elog('ERROR') raise catchable PgErrors (P0001,
-- like PL/pgSQL's RAISE)
CREATE FUNCTION err_raise() RETURNS text LANGUAGE plphp AS $$
	try {
		pg_raise('error', 'user-raised');
	} catch (PgError $e) {
		return "pg_raise: [" . $e->getSQLState() . "] " . $e->getMessage();
	}
$$;
SELECT err_raise();
           err_raise           
-------------------------------
 pg_raise: [P0001] user-raised
(1 row)

CREATE FUNCTION err_elog() RETURNS text LANGUAGE plphp AS $$
	try {
		elog('ERROR', 'user-raised too');
	} catch (PgError $e) {
		return "elog: [" . $e->getSQLState() . "] " . $e->getMessage();
	}
$$;
SELECT err_elog();
           err_elog            
-------------------------------
 elog: [P0001] user-raised too
(1 row)

-- An uncaught PgError aborts the statement with the original message
CREATE FUNCTION err_uncaught() RETURNS void LANGUAGE plphp AS $$
	spi_exec("select 1/0");
$$;
SELECT err_uncaught();
ERROR:  division by zero at line 2
CONTEXT:  PL/php function "err_uncaught"
-- Errors surfacing mid-iteration from a cursor are catchable too
CREATE FUNCTION err_cursor() RETURNS text LANGUAGE plphp AS $$
	$c = spi_query("select 1/(g-2) as x from generate_series(1,3) g");
	$vals = array();
	try {
		while ($row = spi_fetchrow($c))
			$vals[] = $row['x'];
	} catch (PgError $e) {
		spi_cursor_close($c);
		return implode(",", $vals) . " then " . $e->getMessage();
	}
	return "not reached";
$$;
SELECT err_cursor();
        err_cursor        
--------------------------
 -1 then division by zero
(1 row)

-- An error crossing nested PL/php calls: propagates cleanly (this used to
-- crash the backend via a stale Zend bailout environment), is catchable in
-- the outer function, and the session stays healthy
CREATE FUNCTION err_inner() RETURNS int LANGUAGE plphp AS $$
	spi_exec("select 1/0");
$$;
CREATE FUNCTION err_outer() RETURNS int LANGUAGE plphp AS $$
	$r = spi_exec("select err_inner() as v");
	return 1;
$$;
SELECT err_outer();
ERROR:  division by zero at line 2
CONTEXT:  PL/php function "err_outer"
SELECT 1 AS session_alive;
 session_alive 
---------------
             1
(1 row)

CREATE FUNCTION err_outer_catch() RETURNS text LANGUAGE plphp AS $$
	try {
		spi_exec("select err_inner()");
	} catch (PgError $e) {
		return "caught nested: " . $e->getMessage();
	}
	return "not reached";
$$;
SELECT err_outer_catch();
              err_outer_catch              
-------------------------------------------
 caught nested: division by zero at line 2
(1 row)

-- pg_raise can attach DETAIL, HINT and a custom SQLSTATE, like PL/pgSQL's
-- RAISE ... USING.  Caught, all four fields are readable.
CREATE FUNCTION err_raise_using() RETURNS text LANGUAGE plphp AS $$
	try {
		pg_raise('error', 'bad thing', 'because reasons', 'do X', '22023');
	} catch (PgError $e) {
		return sprintf("[%s] %s / detail=%s / hint=%s",
			$e->getSQLState(), $e->getMessage(), $e->getDetail(), $e->getHint());
	}
$$;
SELECT err_raise_using();
                    err_raise_using                     
--------------------------------------------------------
 [22023] bad thing / detail=because reasons / hint=do X
(1 row)

-- The custom SQLSTATE/detail/hint survive an uncaught trip out through the
-- PostgreSQL error layer and back into a PgError caught one call up.
CREATE FUNCTION err_custom_inner() RETURNS void LANGUAGE plphp AS $$
	pg_raise('error', 'custom failure', 'the gory details', 'try harder', '22012');
$$;
CREATE FUNCTION err_custom_outer() RETURNS text LANGUAGE plphp AS $$
	try {
		spi_exec("select err_custom_inner()");
	} catch (PgError $e) {
		return $e->getSQLState() . " | " . $e->getDetail() . " | " . $e->getHint();
	}
	return "not reached";
$$;
SELECT err_custom_outer();
           err_custom_outer            
---------------------------------------
 22012 | the gory details | try harder
(1 row)

-- Uncaught, the DETAIL and HINT lines show up in the error itself (psql
-- prints them at default verbosity).
CREATE FUNCTION err_uncaught_using() RETURNS void LANGUAGE plphp AS $$
	pg_raise('error', 'boom', 'what went wrong', 'what to do');
$$;
SELECT err_uncaught_using();
ERROR:  boom at line 2
DETAIL:  what went wrong
HINT:  what to do
CONTEXT:  PL/php function "err_uncaught_using"
-- A NOTICE can carry DETAIL/HINT too.
CREATE FUNCTION note_using() RETURNS void LANGUAGE plphp AS $$
	pg_raise('notice', 'heads up', 'more info', 'a suggestion');
$$;
SELECT note_using();
NOTICE:  heads up
DETAIL:  more info
HINT:  a suggestion
 note_using 
------------
 
(1 row)

-- An invalid SQLSTATE (not five upper-case/digit characters) is rejected.
CREATE FUNCTION err_bad_sqlstate() RETURNS void LANGUAGE plphp AS $$
	pg_raise('error', 'x', null, null, 'abcde');
$$;
SELECT err_bad_sqlstate();
ERROR:  pg_raise: SQLSTATE must contain only digits and upper-case ASCII letters at line 2
CONTEXT:  PL/php function "err_bad_sqlstate"
DROP FUNCTION err_raise_using(), err_custom_inner(), err_custom_outer(),
	err_uncaught_using(), note_using(), err_bad_sqlstate();
DROP FUNCTION err_inner(), err_outer(), err_outer_catch();
DROP TABLE errt;