Implement two new special variables in PL/PgSQL: SQLSTATE and SQLERRM.

These contain the SQLSTATE and error message of the current exception,
respectively. They are scope-local variables that are only defined
in exception handlers (so attempting to reference them outside an
exception handler is an error). Update the regression tests and the
documentation.

Also, do some minor related cleanup: export an unpack_sql_state()
function from the backend and use it to unpack a SQLSTATE into a
string, and add a free_var() function to pl_exec.c

Original patch from Pavel Stehule, review by Neil Conway.

2 files changed, 98 insertions, 0 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 39e61e09cf2..2650cbb908a 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2415,3 +2415,57 @@ NOTICE:  10 15 20
 
 drop table eifoo cascade;
 drop type eitype cascade;
+--
+-- SQLSTATE and SQLERRM test
+--
+-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+-- blocks
+create function excpt_test() returns void as $$
+begin
+    raise notice '% %', sqlstate, sqlerrm;
+end; $$ language plpgsql;
+ERROR:  syntax error at or near "sqlstate" at character 79
+LINE 3:     raise notice '% %', sqlstate, sqlerrm;
+                                ^
+-- should fail
+create function excpt_test() returns void as $$
+begin
+    begin
+        begin
+    	    raise notice '% %', sqlstate, sqlerrm;
+        end;
+    end;
+end; $$ language plpgsql;
+ERROR:  syntax error at or near "sqlstate" at character 108
+LINE 5:          raise notice '% %', sqlstate, sqlerrm;
+                                     ^
+create function excpt_test() returns void as $$
+begin
+    begin
+    	raise exception 'user exception';
+    exception when others then
+	    raise notice 'caught exception % %', sqlstate, sqlerrm;
+	    begin
+	        raise notice '% %', sqlstate, sqlerrm;
+	        perform 10/0;
+        exception
+            when substring_error then
+                -- this exception handler shouldn't be invoked
+                raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
+	        when division_by_zero then
+	            raise notice 'caught exception % %', sqlstate, sqlerrm;
+	    end;
+	    raise notice '% %', sqlstate, sqlerrm;
+    end;
+end; $$ language plpgsql;
+select excpt_test();
+NOTICE:  caught exception P0001 user exception
+NOTICE:  P0001 user exception
+NOTICE:  caught exception 22012 division by zero
+NOTICE:  P0001 user exception
+ excpt_test 
+------------
+ 
+(1 row)
+
+drop function excpt_test();
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 314f69915fc..9dc00f2f1e5 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2050,3 +2050,47 @@ select execute_into_test('eifoo');
 
 drop table eifoo cascade;
 drop type eitype cascade;
+
+--
+-- SQLSTATE and SQLERRM test
+--
+
+-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+-- blocks
+create function excpt_test() returns void as $$
+begin
+    raise notice '% %', sqlstate, sqlerrm;
+end; $$ language plpgsql;
+
+-- should fail
+create function excpt_test() returns void as $$
+begin
+    begin
+        begin
+    	    raise notice '% %', sqlstate, sqlerrm;
+        end;
+    end;
+end; $$ language plpgsql;
+
+create function excpt_test() returns void as $$
+begin
+    begin
+    	raise exception 'user exception';
+    exception when others then
+	    raise notice 'caught exception % %', sqlstate, sqlerrm;
+	    begin
+	        raise notice '% %', sqlstate, sqlerrm;
+	        perform 10/0;
+        exception
+            when substring_error then
+                -- this exception handler shouldn't be invoked
+                raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
+	        when division_by_zero then
+	            raise notice 'caught exception % %', sqlstate, sqlerrm;
+	    end;
+	    raise notice '% %', sqlstate, sqlerrm;
+    end;
+end; $$ language plpgsql;
+
+select excpt_test();
+drop function excpt_test();