Add PL/pgSQL SQLSTATE and SQLERRM support which sets these values on

error.

Pavel Stehule

2 files changed, 46 insertions, 0 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 08fbe46b3a2..5ee5c338e46 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2380,3 +2380,29 @@ ERROR:  control reached end of function without RETURN
 CONTEXT:  PL/pgSQL function "missing_return_expr"
 drop function void_return_expr();
 drop function missing_return_expr();
+-- test SQLSTATE and SQLERRM
+create or replace function trap_exceptions() returns void as $_$
+begin
+   begin
+     raise exception 'first exception';
+   exception when others then
+     raise notice '% %', SQLSTATE, SQLERRM;
+   end;
+   raise notice '% %', SQLSTATE, SQLERRM;
+   begin
+     raise exception 'last exception';
+   exception when others then
+     raise notice '% %', SQLSTATE, SQLERRM;
+   end;
+   return;
+end; $_$ language plpgsql;
+select trap_exceptions();
+NOTICE:  P0001 first exception
+NOTICE:  00000 Sucessful completion
+NOTICE:  P0001 last exception
+ trap_exceptions 
+-----------------
+ 
+(1 row)
+
+drop function trap_exceptions();
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 7ea7c8c6e0c..e2ef2ec5fe3 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2018,3 +2018,23 @@ select missing_return_expr();
 
 drop function void_return_expr();
 drop function missing_return_expr();
+-- test SQLSTATE and SQLERRM
+create or replace function trap_exceptions() returns void as $_$
+begin
+   begin
+     raise exception 'first exception';
+   exception when others then
+     raise notice '% %', SQLSTATE, SQLERRM;
+   end;
+   raise notice '% %', SQLSTATE, SQLERRM;
+   begin
+     raise exception 'last exception';
+   exception when others then
+     raise notice '% %', SQLSTATE, SQLERRM;
+   end;
+   return;
+end; $_$ language plpgsql;
+
+select trap_exceptions();
+
+drop function trap_exceptions();