Improve plpgsql's RAISE command. It is now possible to attach DETAIL and

HINT fields to a user-thrown error message, and to specify the SQLSTATE
error code to use.  The syntax has also been tweaked so that the
Oracle-compatible case "RAISE exception_name" works (though you won't get a
very nice error message if you just write that much).  Lastly, support
the Oracle-compatible syntax "RAISE" with no parameters to re-throw
the current error from within an EXCEPTION block.

In passing, allow the syntax SQLSTATE 'nnnnn' within EXCEPTION lists,
so that there is a way to trap errors with custom SQLSTATE codes.

Pavel Stehule and Tom Lane

2 files changed, 260 insertions, 2 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index da987b22e46..9f18f45126a 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3267,7 +3267,7 @@ end;
 $$ language plpgsql;
 ERROR:  cursor FOR loop must use a bound cursor variable
 CONTEXT:  compile of PL/pgSQL function "forc_bad" near line 4
--- return query execute
+-- test RETURN QUERY EXECUTE
 create or replace function return_dquery()
 returns setof int as $$
 begin
@@ -3285,3 +3285,132 @@ select * from return_dquery();
 (4 rows)
 
 drop function return_dquery();
+-- Tests for 8.4's new RAISE features
+create or replace function raise_test() returns void as $$
+begin
+  raise notice '% % %', 1, 2, 3 
+     using errcode = '55001', detail = 'some detail info', hint = 'some hint';
+  raise '% % %', 1, 2, 3 
+     using errcode = 'division_by_zero', detail = 'some detail info';
+end;
+$$ language plpgsql;
+select raise_test();
+NOTICE:  1 2 3
+DETAIL:  some detail info
+HINT:  some hint
+ERROR:  1 2 3
+DETAIL:  some detail info
+-- Since we can't actually see the thrown SQLSTATE in default psql output,
+-- test it like this; this also tests re-RAISE
+create or replace function raise_test() returns void as $$
+begin
+  raise 'check me'
+     using errcode = 'division_by_zero', detail = 'some detail info';
+  exception
+    when others then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+select raise_test();
+NOTICE:  SQLSTATE: 22012 SQLERRM: check me
+ERROR:  check me
+DETAIL:  some detail info
+create or replace function raise_test() returns void as $$
+begin
+  raise 'check me'
+     using errcode = '1234F', detail = 'some detail info';
+  exception
+    when others then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+select raise_test();
+NOTICE:  SQLSTATE: 1234F SQLERRM: check me
+ERROR:  check me
+DETAIL:  some detail info
+-- SQLSTATE specification in WHEN
+create or replace function raise_test() returns void as $$
+begin
+  raise 'check me'
+     using errcode = '1234F', detail = 'some detail info';
+  exception
+    when sqlstate '1234F' then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+select raise_test();
+NOTICE:  SQLSTATE: 1234F SQLERRM: check me
+ERROR:  check me
+DETAIL:  some detail info
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero using detail = 'some detail info';
+  exception
+    when others then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+select raise_test();
+NOTICE:  SQLSTATE: 22012 SQLERRM: division_by_zero
+ERROR:  division_by_zero
+DETAIL:  some detail info
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero;
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  division_by_zero
+create or replace function raise_test() returns void as $$
+begin
+  raise sqlstate '1234F';
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  1234F
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero using message = 'custom' || ' message';
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  custom message
+create or replace function raise_test() returns void as $$
+begin
+  raise using message = 'custom' || ' message', errcode = '22012';
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  custom message
+-- conflict on message
+create or replace function raise_test() returns void as $$
+begin
+  raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  RAISE option already specified: MESSAGE
+CONTEXT:  PL/pgSQL function "raise_test" line 2 at RAISE
+-- conflict on errcode
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  RAISE option already specified: ERRCODE
+CONTEXT:  PL/pgSQL function "raise_test" line 2 at RAISE
+-- nothing to re-RAISE
+create or replace function raise_test() returns void as $$
+begin
+  raise;
+end;
+$$ language plpgsql;
+select raise_test();
+ERROR:  RAISE without parameters cannot be used outside an exception handler
+CONTEXT:  PL/pgSQL function "raise_test"
+drop function raise_test();
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index b0799dcdc70..f64bfabc1e5 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2670,7 +2670,7 @@ begin
 end;
 $$ language plpgsql;
 
--- return query execute
+-- test RETURN QUERY EXECUTE
 
 create or replace function return_dquery()
 returns setof int as $$
@@ -2683,3 +2683,132 @@ $$ language plpgsql;
 select * from return_dquery();
 
 drop function return_dquery();
+
+-- Tests for 8.4's new RAISE features
+
+create or replace function raise_test() returns void as $$
+begin
+  raise notice '% % %', 1, 2, 3 
+     using errcode = '55001', detail = 'some detail info', hint = 'some hint';
+  raise '% % %', 1, 2, 3 
+     using errcode = 'division_by_zero', detail = 'some detail info';
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+-- Since we can't actually see the thrown SQLSTATE in default psql output,
+-- test it like this; this also tests re-RAISE
+
+create or replace function raise_test() returns void as $$
+begin
+  raise 'check me'
+     using errcode = 'division_by_zero', detail = 'some detail info';
+  exception
+    when others then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+create or replace function raise_test() returns void as $$
+begin
+  raise 'check me'
+     using errcode = '1234F', detail = 'some detail info';
+  exception
+    when others then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+-- SQLSTATE specification in WHEN
+create or replace function raise_test() returns void as $$
+begin
+  raise 'check me'
+     using errcode = '1234F', detail = 'some detail info';
+  exception
+    when sqlstate '1234F' then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero using detail = 'some detail info';
+  exception
+    when others then
+      raise notice 'SQLSTATE: % SQLERRM: %', sqlstate, sqlerrm;
+      raise;
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero;
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+create or replace function raise_test() returns void as $$
+begin
+  raise sqlstate '1234F';
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero using message = 'custom' || ' message';
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+create or replace function raise_test() returns void as $$
+begin
+  raise using message = 'custom' || ' message', errcode = '22012';
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+-- conflict on message
+create or replace function raise_test() returns void as $$
+begin
+  raise notice 'some message' using message = 'custom' || ' message', errcode = '22012';
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+-- conflict on errcode
+create or replace function raise_test() returns void as $$
+begin
+  raise division_by_zero using message = 'custom' || ' message', errcode = '22012';
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+-- nothing to re-RAISE
+create or replace function raise_test() returns void as $$
+begin
+  raise;
+end;
+$$ language plpgsql;
+
+select raise_test();
+
+drop function raise_test();