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();