plpgsql does exceptions.

There are still some things that need refinement; in particular I fear
that the recognized set of error condition names probably has little in
common with what Oracle recognizes.  But it's a start.

2 files changed, 90 insertions, 0 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 694165e506a..6907905cc51 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -1793,3 +1793,60 @@ SELECT * FROM perform_test;
 (3 rows)
 
 drop table perform_test;
+--
+-- Test error trapping
+--
+create function trap_zero_divide(int) returns int as $$
+declare x int;
+declare sx smallint;
+begin
+	begin	-- start a subtransaction
+		raise notice 'should see this';
+		x := 100 / $1;
+		raise notice 'should see this only if % <> 0', $1;
+		sx := $1;
+		raise notice 'should see this only if % fits in smallint', $1;
+		if $1 < 0 then
+			raise exception '% is less than zero', $1;
+		end if;
+	exception
+		when division_by_zero then
+			raise notice 'caught division_by_zero';
+			x := -1;
+		when NUMERIC_VALUE_OUT_OF_RANGE then
+			raise notice 'caught numeric_value_out_of_range';
+			x := -2;
+	end;
+	return x;
+end$$ language plpgsql;
+select trap_zero_divide(50);
+NOTICE:  should see this
+NOTICE:  should see this only if 50 <> 0
+NOTICE:  should see this only if 50 fits in smallint
+ trap_zero_divide 
+------------------
+                2
+(1 row)
+
+select trap_zero_divide(0);
+NOTICE:  should see this
+NOTICE:  caught division_by_zero
+ trap_zero_divide 
+------------------
+               -1
+(1 row)
+
+select trap_zero_divide(100000);
+NOTICE:  should see this
+NOTICE:  should see this only if 100000 <> 0
+NOTICE:  caught numeric_value_out_of_range
+ trap_zero_divide 
+------------------
+               -2
+(1 row)
+
+select trap_zero_divide(-100);
+NOTICE:  should see this
+NOTICE:  should see this only if -100 <> 0
+NOTICE:  should see this only if -100 fits in smallint
+ERROR:  -100 is less than zero
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 9cb7f7c9407..48a78196a89 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1608,3 +1608,36 @@ SELECT perform_test_func();
 SELECT * FROM perform_test;
 
 drop table perform_test;
+
+--
+-- Test error trapping
+--
+
+create function trap_zero_divide(int) returns int as $$
+declare x int;
+declare sx smallint;
+begin
+	begin	-- start a subtransaction
+		raise notice 'should see this';
+		x := 100 / $1;
+		raise notice 'should see this only if % <> 0', $1;
+		sx := $1;
+		raise notice 'should see this only if % fits in smallint', $1;
+		if $1 < 0 then
+			raise exception '% is less than zero', $1;
+		end if;
+	exception
+		when division_by_zero then
+			raise notice 'caught division_by_zero';
+			x := -1;
+		when NUMERIC_VALUE_OUT_OF_RANGE then
+			raise notice 'caught numeric_value_out_of_range';
+			x := -2;
+	end;
+	return x;
+end$$ language plpgsql;
+
+select trap_zero_divide(50);
+select trap_zero_divide(0);
+select trap_zero_divide(100000);
+select trap_zero_divide(-100);