Fix some expected output diffs in the alternate expected output for testcase

'plpgsql'

1 files changed, 689 insertions, 12 deletions

diff --git a/src/test/regress/expected/plpgsql_1.out b/src/test/regress/expected/plpgsql_1.out
index a563a3ee20..3f286d35d4 100644
--- a/src/test/regress/expected/plpgsql_1.out
+++ b/src/test/regress/expected/plpgsql_1.out
@@ -2435,7 +2435,20 @@ end;
 $$ language plpgsql;
 select raise_test2(10);
 ERROR:  too few parameters specified for RAISE
-CONTEXT:  PL/pgSQL function raise_test2(integer) line 3 at RAISE
+CONTEXT:  compilation of PL/pgSQL function "raise_test2" near line 3
+create function raise_test3(int) returns int as $$
+begin
+    raise notice 'This message has no parameters (despite having %% signs in it)!';
+    return $1;
+end;
+$$ language plpgsql;
+select raise_test3(1);
+NOTICE:  This message has no parameters (despite having % signs in it)!
+ raise_test3 
+-------------
+           1
+(1 row)
+
 -- Test re-RAISE inside a nested exception block.  This case is allowed
 -- by Oracle's PL/SQL but was handled differently by PG before 9.1.
 CREATE FUNCTION reraise_test() RETURNS void AS $$
@@ -2610,9 +2623,18 @@ end; $$ language plpgsql;
 select excpt_test3();
 ERROR:  Internal subtransactions not supported in Postgres-XL
 CONTEXT:  PL/pgSQL function excpt_test3() line 3 during statement block entry
+create function excpt_test4() returns text as $$
+begin
+	begin perform 1/0;
+	exception when others then return sqlerrm; end;
+end; $$ language plpgsql;
+select excpt_test4();
+ERROR:  Internal subtransactions not supported in Postgres-XL
+CONTEXT:  PL/pgSQL function excpt_test4() line 3 during statement block entry
 drop function excpt_test1();
 drop function excpt_test2();
 drop function excpt_test3();
+drop function excpt_test4();
 -- parameters of raise stmt can be expressions
 create function raise_exprs() returns void as $$
 declare
@@ -2947,9 +2969,12 @@ begin
   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
 end$$ language plpgsql;
 select footest();
-ERROR:  RETURNING clause not yet supported
-CONTEXT:  SQL statement "insert into foo values(5,6) returning *"
-PL/pgSQL function footest() line 5 at SQL statement
+NOTICE:  x.f1 = 5, x.f2 = 6
+ footest 
+---------
+ 
+(1 row)
+
 create or replace function footest() returns void as $$
 declare x record;
 begin
@@ -2969,9 +2994,12 @@ begin
   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
 end$$ language plpgsql;
 select footest();
-ERROR:  RETURNING clause not yet supported
-CONTEXT:  SQL statement "insert into foo values(5,6) returning *"
-PL/pgSQL function footest() line 5 at EXECUTE statement
+NOTICE:  x.f1 = 5, x.f2 = 6
+ footest 
+---------
+ 
+(1 row)
+
 create or replace function footest() returns void as $$
 declare x record;
 begin
@@ -2980,15 +3008,22 @@ begin
   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
 end$$ language plpgsql;
 select footest();
-ERROR:  RETURNING clause not yet supported
-CONTEXT:  SQL statement "insert into foo values(7,8),(9,10) returning *"
-PL/pgSQL function footest() line 5 at EXECUTE statement
+NOTICE:  x.f1 = 9, x.f2 = 10
+ footest 
+---------
+ 
+(1 row)
+
 select * from foo order by 1, 2;
  f1 | f2 
 ----+----
   1 |  2
   3 |  4
-(2 rows)
+  5 |  6
+  5 |  6
+  7 |  8
+  9 | 10
+(6 rows)
 
 create or replace function footest() returns void as $$
 declare x record;
@@ -3056,9 +3091,237 @@ begin
   raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
 end$$ language plpgsql;
 select footest();
-ERROR:  query returned no rows
+ERROR:  query returned more than one row
 CONTEXT:  PL/pgSQL function footest() line 5 at EXECUTE statement
 drop function footest();
+-- test printing parameters after failure due to STRICT
+set plpgsql.print_strict_params to true;
+create or replace function footest() returns void as $$
+declare
+x record;
+p1 int := 2;
+p3 text := 'foo';
+begin
+  -- no rows
+  select * from foo where f1 = p1 and f1::text = p3 into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned no rows
+DETAIL:  parameters: p1 = '2', p3 = 'foo'
+CONTEXT:  PL/pgSQL function footest() line 8 at SQL statement
+create or replace function footest() returns void as $$
+declare
+x record;
+p1 int := 2;
+p3 text := 'foo';
+begin
+  -- too many rows
+  select * from foo where f1 > p1 or f1::text = p3  into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned more than one row
+DETAIL:  parameters: p1 = '2', p3 = 'foo'
+CONTEXT:  PL/pgSQL function footest() line 8 at SQL statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+  -- too many rows, no params
+  select * from foo where f1 > 3 into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned more than one row
+CONTEXT:  PL/pgSQL function footest() line 5 at SQL statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+  -- no rows
+  execute 'select * from foo where f1 = $1 or f1::text = $2' using 0, 'foo' into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned no rows
+DETAIL:  parameters: $1 = '0', $2 = 'foo'
+CONTEXT:  PL/pgSQL function footest() line 5 at EXECUTE statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+  -- too many rows
+  execute 'select * from foo where f1 > $1' using 1 into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned more than one row
+DETAIL:  parameters: $1 = '1'
+CONTEXT:  PL/pgSQL function footest() line 5 at EXECUTE statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+  -- too many rows, no parameters
+  execute 'select * from foo where f1 > 3' into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned more than one row
+CONTEXT:  PL/pgSQL function footest() line 5 at EXECUTE statement
+create or replace function footest() returns void as $$
+-- override the global
+#print_strict_params off
+declare
+x record;
+p1 int := 2;
+p3 text := 'foo';
+begin
+  -- too many rows
+  select * from foo where f1 > p1 or f1::text = p3  into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned more than one row
+CONTEXT:  PL/pgSQL function footest() line 10 at SQL statement
+reset plpgsql.print_strict_params;
+create or replace function footest() returns void as $$
+-- override the global
+#print_strict_params on
+declare
+x record;
+p1 int := 2;
+p3 text := 'foo';
+begin
+  -- too many rows
+  select * from foo where f1 > p1 or f1::text = p3  into strict x;
+  raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR:  query returned more than one row
+DETAIL:  parameters: p1 = '2', p3 = 'foo'
+CONTEXT:  PL/pgSQL function footest() line 10 at SQL statement
+-- test warnings and errors
+set plpgsql.extra_warnings to 'all';
+ERROR:  syntax error at or near "all"
+set plpgsql.extra_warnings to 'none';
+set plpgsql.extra_errors to 'all';
+ERROR:  syntax error at or near "all"
+set plpgsql.extra_errors to 'none';
+-- test warnings when shadowing a variable
+set plpgsql.extra_warnings to 'shadowed_variables';
+-- simple shadowing of input and output parameters
+create or replace function shadowtest(in1 int)
+	returns table (out1 int) as $$
+declare
+in1 int;
+out1 int;
+begin
+end
+$$ language plpgsql;
+WARNING:  variable "in1" shadows a previously defined variable
+LINE 4: in1 int;
+        ^
+WARNING:  variable "out1" shadows a previously defined variable
+LINE 5: out1 int;
+        ^
+select shadowtest(1);
+ shadowtest 
+------------
+(0 rows)
+
+set plpgsql.extra_warnings to 'shadowed_variables';
+select shadowtest(1);
+ shadowtest 
+------------
+(0 rows)
+
+create or replace function shadowtest(in1 int)
+	returns table (out1 int) as $$
+declare
+in1 int;
+out1 int;
+begin
+end
+$$ language plpgsql;
+WARNING:  variable "in1" shadows a previously defined variable
+LINE 4: in1 int;
+        ^
+WARNING:  variable "out1" shadows a previously defined variable
+LINE 5: out1 int;
+        ^
+select shadowtest(1);
+ shadowtest 
+------------
+(0 rows)
+
+drop function shadowtest(int);
+-- shadowing in a second DECLARE block
+create or replace function shadowtest()
+	returns void as $$
+declare
+f1 int;
+begin
+	declare
+	f1 int;
+	begin
+	end;
+end$$ language plpgsql;
+WARNING:  variable "f1" shadows a previously defined variable
+LINE 7:  f1 int;
+         ^
+drop function shadowtest();
+-- several levels of shadowing
+create or replace function shadowtest(in1 int)
+	returns void as $$
+declare
+in1 int;
+begin
+	declare
+	in1 int;
+	begin
+	end;
+end$$ language plpgsql;
+WARNING:  variable "in1" shadows a previously defined variable
+LINE 4: in1 int;
+        ^
+WARNING:  variable "in1" shadows a previously defined variable
+LINE 7:  in1 int;
+         ^
+drop function shadowtest(int);
+-- shadowing in cursor definitions
+create or replace function shadowtest()
+	returns void as $$
+declare
+f1 int;
+c1 cursor (f1 int) for select 1;
+begin
+end$$ language plpgsql;
+WARNING:  variable "f1" shadows a previously defined variable
+LINE 5: c1 cursor (f1 int) for select 1;
+                   ^
+drop function shadowtest();
+-- test errors when shadowing a variable
+set plpgsql.extra_errors to 'shadowed_variables';
+create or replace function shadowtest(f1 int)
+	returns boolean as $$
+declare f1 int; begin return 1; end $$ language plpgsql;
+ERROR:  variable "f1" shadows a previously defined variable
+LINE 3: declare f1 int; begin return 1; end $$ language plpgsql;
+                ^
+select shadowtest(1);
+ERROR:  function shadowtest(integer) does not exist
+LINE 1: select shadowtest(1);
+               ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+reset plpgsql.extra_errors;
+reset plpgsql.extra_warnings;
+create or replace function shadowtest(f1 int)
+	returns boolean as $$
+declare f1 int; begin return 1; end $$ language plpgsql;
+select shadowtest(1);
+ shadowtest 
+------------
+ t
+(1 row)
+
 -- test scrollable cursor support
 create function sc_test() returns setof integer as $$
 declare
@@ -3557,6 +3820,137 @@ select * from returnqueryf() order by 1,2,3;
 
 drop function returnqueryf();
 drop table tabwithcols;
+--
+-- Tests for composite-type results
+--
+create type compostype as (x int, y varchar);
+-- test: use of variable of composite type in return statement
+create or replace function compos() returns compostype as $$
+declare
+  v compostype;
+begin
+  v := (1, 'hello');
+  return v;
+end;
+$$ language plpgsql;
+select compos();
+  compos   
+-----------
+ (1,hello)
+(1 row)
+
+-- test: use of variable of record type in return statement
+create or replace function compos() returns compostype as $$
+declare
+  v record;
+begin
+  v := (1, 'hello'::varchar);
+  return v;
+end;
+$$ language plpgsql;
+select compos();
+  compos   
+-----------
+ (1,hello)
+(1 row)
+
+-- test: use of row expr in return statement
+create or replace function compos() returns compostype as $$
+begin
+  return (1, 'hello'::varchar);
+end;
+$$ language plpgsql;
+select compos();
+  compos   
+-----------
+ (1,hello)
+(1 row)
+
+-- this does not work currently (no implicit casting)
+create or replace function compos() returns compostype as $$
+begin
+  return (1, 'hello');
+end;
+$$ language plpgsql;
+select compos();
+ERROR:  returned record type does not match expected record type
+DETAIL:  Returned type unknown does not match expected type character varying in column 2.
+CONTEXT:  PL/pgSQL function compos() while casting return value to function's return type
+-- ... but this does
+create or replace function compos() returns compostype as $$
+begin
+  return (1, 'hello')::compostype;
+end;
+$$ language plpgsql;
+select compos();
+  compos   
+-----------
+ (1,hello)
+(1 row)
+
+drop function compos();
+-- test: return a row expr as record.
+create or replace function composrec() returns record as $$
+declare
+  v record;
+begin
+  v := (1, 'hello');
+  return v;
+end;
+$$ language plpgsql;
+select composrec();
+ composrec 
+-----------
+ (1,hello)
+(1 row)
+
+-- test: return row expr in return statement.
+create or replace function composrec() returns record as $$
+begin
+  return (1, 'hello');
+end;
+$$ language plpgsql;
+select composrec();
+ composrec 
+-----------
+ (1,hello)
+(1 row)
+
+drop function composrec();
+-- test: row expr in RETURN NEXT statement.
+create or replace function compos() returns setof compostype as $$
+begin
+  for i in 1..3
+  loop
+    return next (1, 'hello'::varchar);
+  end loop;
+  return next null::compostype;
+  return next (2, 'goodbye')::compostype;
+end;
+$$ language plpgsql;
+select * from compos();
+ x |    y    
+---+---------
+ 1 | hello
+ 1 | hello
+ 1 | hello
+   | 
+ 2 | goodbye
+(5 rows)
+
+drop function compos();
+-- test: use invalid expr in return statement.
+create or replace function compos() returns compostype as $$
+begin
+  return 1 + 1;
+end;
+$$ language plpgsql;
+select compos();
+ERROR:  cannot return non-composite value from function returning composite type
+CONTEXT:  PL/pgSQL function compos() line 3 at RETURN
+drop function compos();
+drop type compostype;
+--
 -- Tests for 8.4's new RAISE features
 create or replace function raise_test() returns void as $$
 begin
@@ -3763,6 +4157,36 @@ select raise_test();
 ERROR:  Internal subtransactions not supported in Postgres-XL
 CONTEXT:  PL/pgSQL function raise_test() line 2 during statement block entry
 drop function raise_test();
+-- test passing column_name, constraint_name, datatype_name, table_name
+-- and schema_name error fields
+create or replace function stacked_diagnostics_test() returns void as $$
+declare _column_name text;
+        _constraint_name text;
+        _datatype_name text;
+        _table_name text;
+        _schema_name text;
+begin
+  raise exception using
+    column = '>>some column name<<',
+    constraint = '>>some constraint name<<',
+    datatype = '>>some datatype name<<',
+    table = '>>some table name<<',
+    schema = '>>some schema name<<';
+exception when others then
+  get stacked diagnostics
+        _column_name = column_name,
+        _constraint_name = constraint_name,
+        _datatype_name = pg_datatype_name,
+        _table_name = table_name,
+        _schema_name = schema_name;
+  raise notice 'column %, constraint %, type %, table %, schema %',
+    _column_name, _constraint_name, _datatype_name, _table_name, _schema_name;
+end;
+$$ language plpgsql;
+select stacked_diagnostics_test();
+ERROR:  Internal subtransactions not supported in Postgres-XL
+CONTEXT:  PL/pgSQL function stacked_diagnostics_test() line 7 during statement block entry
+drop function stacked_diagnostics_test();
 -- test CASE statement
 create or replace function case_test(bigint) returns text as $$
 declare a int = 10;
@@ -4159,6 +4583,21 @@ ERROR:  current transaction is aborted, commands ignored until end of transactio
 rollback;
 drop function error2(p_name_table text);
 drop function error1(text);
+-- Test for consistent reporting of error context
+create function fail() returns int language plpgsql as $$
+begin
+  return 1/0;
+end
+$$;
+select fail();
+ERROR:  division by zero
+CONTEXT:  SQL statement "SELECT 1/0"
+PL/pgSQL function fail() line 3 at RETURN
+select fail();
+ERROR:  division by zero
+CONTEXT:  SQL statement "SELECT 1/0"
+PL/pgSQL function fail() line 3 at RETURN
+drop function fail();
 -- Test handling of string literals.
 set standard_conforming_strings = off;
 create or replace function strtest() returns text as $$
@@ -4270,6 +4709,27 @@ LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...
                                         ^
 QUERY:  SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
 CONTEXT:  PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows
+-- Check handling of errors thrown from/into anonymous code blocks.
+do $outer$
+begin
+  for i in 1..10 loop
+   begin
+    execute $ex$
+      do $$
+      declare x int = 0;
+      begin
+        x := 1 / x;
+      end;
+      $$;
+    $ex$;
+  exception when division_by_zero then
+    raise notice 'caught division by zero';
+  end;
+  end loop;
+end;
+$outer$;
+ERROR:  Internal subtransactions not supported in Postgres-XL
+CONTEXT:  PL/pgSQL function inline_code_block line 4 during statement block entry
 -- Check variable scoping -- a var is not available in its own or prior
 -- default expressions.
 create function scope_test() returns int as $$
@@ -4364,6 +4824,20 @@ select unreserved_test();
               42
 (1 row)
 
+create or replace function unreserved_test() returns int as $$
+declare
+  return int := 42;
+begin
+  return := return + 1;
+  return return;
+end
+$$ language plpgsql;
+select unreserved_test();
+ unreserved_test 
+-----------------
+              43
+(1 row)
+
 drop function unreserved_test();
 --
 -- Test FOREACH over arrays
@@ -4750,3 +5224,206 @@ PL/pgSQL function testjointemptable() line 8 at SQL statement
 DROP TABLE RealTable;
 DROP TABLE TmpBar;
 DROP TABLE TmpFoo;
+-- access to call stack
+create function inner_func(int)
+returns int as $$
+declare _context text;
+begin
+  get diagnostics _context = pg_context;
+  raise notice '***%***', _context;
+  -- lets do it again, just for fun..
+  get diagnostics _context = pg_context;
+  raise notice '***%***', _context;
+  raise notice 'lets make sure we didnt break anything';
+  return 2 * $1;
+end;
+$$ language plpgsql;
+create or replace function outer_func(int)
+returns int as $$
+declare
+  myresult int;
+begin
+  raise notice 'calling down into inner_func()';
+  myresult := inner_func($1);
+  raise notice 'inner_func() done';
+  return myresult;
+end;
+$$ language plpgsql;
+create or replace function outer_outer_func(int)
+returns int as $$
+declare
+  myresult int;
+begin
+  raise notice 'calling down into outer_func()';
+  myresult := outer_func($1);
+  raise notice 'outer_func() done';
+  return myresult;
+end;
+$$ language plpgsql;
+select outer_outer_func(10);
+NOTICE:  calling down into outer_func()
+NOTICE:  calling down into inner_func()
+CONTEXT:  PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
+PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
+CONTEXT:  PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS
+PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
+CONTEXT:  PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  lets make sure we didnt break anything
+CONTEXT:  PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  inner_func() done
+CONTEXT:  PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  outer_func() done
+ outer_outer_func 
+------------------
+               20
+(1 row)
+
+-- repeated call should to work
+select outer_outer_func(20);
+NOTICE:  calling down into outer_func()
+NOTICE:  calling down into inner_func()
+CONTEXT:  PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  ***PL/pgSQL function inner_func(integer) line 4 at GET DIAGNOSTICS
+PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
+CONTEXT:  PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  ***PL/pgSQL function inner_func(integer) line 7 at GET DIAGNOSTICS
+PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment***
+CONTEXT:  PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  lets make sure we didnt break anything
+CONTEXT:  PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  inner_func() done
+CONTEXT:  PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+NOTICE:  outer_func() done
+ outer_outer_func 
+------------------
+               40
+(1 row)
+
+drop function outer_outer_func(int);
+drop function outer_func(int);
+drop function inner_func(int);
+-- access to call stack from exception
+create function inner_func(int)
+returns int as $$
+declare
+  _context text;
+  sx int := 5;
+begin
+  begin
+    perform sx / 0;
+  exception
+    when division_by_zero then
+      get diagnostics _context = pg_context;
+      raise notice '***%***', _context;
+  end;
+
+  -- lets do it again, just for fun..
+  get diagnostics _context = pg_context;
+  raise notice '***%***', _context;
+  raise notice 'lets make sure we didnt break anything';
+  return 2 * $1;
+end;
+$$ language plpgsql;
+create or replace function outer_func(int)
+returns int as $$
+declare
+  myresult int;
+begin
+  raise notice 'calling down into inner_func()';
+  myresult := inner_func($1);
+  raise notice 'inner_func() done';
+  return myresult;
+end;
+$$ language plpgsql;
+create or replace function outer_outer_func(int)
+returns int as $$
+declare
+  myresult int;
+begin
+  raise notice 'calling down into outer_func()';
+  myresult := outer_func($1);
+  raise notice 'outer_func() done';
+  return myresult;
+end;
+$$ language plpgsql;
+select outer_outer_func(10);
+NOTICE:  calling down into outer_func()
+NOTICE:  calling down into inner_func()
+CONTEXT:  PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+ERROR:  Internal subtransactions not supported in Postgres-XL
+CONTEXT:  PL/pgSQL function inner_func(integer) line 6 during statement block entry
+PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+-- repeated call should to work
+select outer_outer_func(20);
+NOTICE:  calling down into outer_func()
+NOTICE:  calling down into inner_func()
+CONTEXT:  PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+ERROR:  Internal subtransactions not supported in Postgres-XL
+CONTEXT:  PL/pgSQL function inner_func(integer) line 6 during statement block entry
+PL/pgSQL function outer_func(integer) line 6 at assignment
+PL/pgSQL function outer_outer_func(integer) line 6 at assignment
+drop function outer_outer_func(int);
+drop function outer_func(int);
+drop function inner_func(int);
+--
+-- Test ASSERT
+--
+do $$
+begin
+  assert 1=1;  -- should succeed
+end;
+$$;
+do $$
+begin
+  assert 1=0;  -- should fail
+end;
+$$;
+ERROR:  assertion failed
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at ASSERT
+do $$
+begin
+  assert NULL;  -- should fail
+end;
+$$;
+ERROR:  assertion failed
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at ASSERT
+-- check controlling GUC
+set plpgsql.check_asserts = off;
+do $$
+begin
+  assert 1=0;  -- won't be tested
+end;
+$$;
+reset plpgsql.check_asserts;
+-- test custom message
+do $$
+declare var text := 'some value';
+begin
+  assert 1=0, format('assertion failed, var = "%s"', var);
+end;
+$$;
+ERROR:  assertion failed, var = "some value"
+CONTEXT:  PL/pgSQL function inline_code_block line 4 at ASSERT
+-- ensure assertions are not trapped by 'others'
+do $$
+begin
+  assert 1=0, 'unhandled assertion';
+exception when others then
+  null; -- do nothing
+end;
+$$;
+ERROR:  Internal subtransactions not supported in Postgres-XL
+CONTEXT:  PL/pgSQL function inline_code_block line 2 during statement block entry