load 'plpgsql';
create extension if not exists plpgsql_check;
NOTICE:  extension "plpgsql_check" already exists, skipping
set client_min_messages to notice;
set plpgsql_check.regress_test_mode = true;
create table zero_columns();
create function assign_empty_tupdesc() returns int as $$
declare
  v int;
begin
  select * from zero_columns into v;
  execute 'select * from zero_columns' into v;
  return v;
end;
$$ language plpgsql;
select * from plpgsql_check_function('assign_empty_tupdesc()');
                        plpgsql_check_function                         
-----------------------------------------------------------------------
 warning:00000:5:SQL statement:too few attributes for target variables
 Query: select * from zero_columns
 --     ^
 Detail: There are more target variables than output columns in query.
 Hint: Check target variables in SELECT INTO statement.
 Context: at SQL statement to v variables
 warning:00000:6:EXECUTE:too few attributes for target variables
 Query: select * from zero_columns
 --     ^
 Detail: There are more target variables than output columns in query.
 Hint: Check target variables in SELECT INTO statement.
 Context: at EXECUTE to v variables
(12 rows)

drop function assign_empty_tupdesc();
drop table zero_columns;
create function public.int_eq(int, int) returns bool as $$
  select $1 = $2;
$$ language sql;
create operator public.=== (
  leftarg = int, rightarg = int, procedure = public.int_eq
);
create function use_custom_operator() returns bool as $$
begin
  return 1 operator(public.===) 2;
end;
$$ language plpgsql;
select type, schema, name, params from plpgsql_show_dependency_tb('use_custom_operator()');
   type   | schema | name |      params       
----------+--------+------+-------------------
 OPERATOR | public | ===  | (integer,integer)
(1 row)

drop function use_custom_operator();
drop function public.int_eq cascade;
NOTICE:  drop cascades to operator ===(integer,integer)
create function tracked_const_oob() returns void as $$
declare
  str text;
  res int;
begin
  -- assign a string constant, so the strconstvars array is allocated
  str := 'constant';

  -- the dynamic query has much more params than the function has datums
  execute 'select $40'
     into res
    using 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
          11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
          21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
          31, 32, 33, 34, 35, 36, 37, 38, 39, 'x'::text;

  raise notice '%, %', str, res;
end;
$$ language plpgsql;
select * from plpgsql_check_function('tracked_const_oob()');
                                plpgsql_check_function                                
--------------------------------------------------------------------------------------
 warning:42804:10:EXECUTE:target type is different type than source type
 Detail: cast "text" value to "integer" type
 Hint: The input expression type does not have an assignment cast to the target type.
 Context: at EXECUTE to variable "res" declared on line 4
(4 rows)

drop function tracked_const_oob();
create table returned_expr_tab(a int);
create function returned_expr_null_node() returns setof refcursor as $$
begin
  return query select a::text::refcursor from returned_expr_tab;
end;
$$ language plpgsql;
select * from plpgsql_check_function('returned_expr_null_node()',
                                     compatibility_warnings => true);
                               plpgsql_check_function                                
-------------------------------------------------------------------------------------
 compatibility:00000:3:RETURN QUERY:obsolete setting of refcursor or cursor variable
 Detail: Internal name of cursor should not be specified by users.
(2 rows)

drop function returned_expr_null_node();
drop table returned_expr_tab;
create table rvalue_tab(a int);
create function rvalue_null_node() returns void as $$
declare
  c refcursor;
begin
  c := (select a::text::refcursor from rvalue_tab limit 1);
  raise notice '%', c;
end;
$$ language plpgsql;
select * from plpgsql_check_function('rvalue_null_node()',
                                     compatibility_warnings => true);
 plpgsql_check_function 
------------------------
(0 rows)

drop function rvalue_null_node();
drop table rvalue_tab;
create table assert_column_tab(a int);
create function pragma_assert_column() returns void as $$
declare
  tname text default 'assert_column_tab';
begin
  -- ASSERT-COLUMN expects two variables, only one is passed
  perform plpgsql_check_pragma('assert-column: tname');
  raise notice '%', tname;
end;
$$ language plpgsql;
select * from plpgsql_check_function('pragma_assert_column()');
WARNING:  "assert-column" on line 6 is not processed.
DETAIL:  too few variables for "assert-column" pragma
 plpgsql_check_function 
------------------------
(0 rows)

drop function pragma_assert_column();
drop table assert_column_tab;
create function pragma_sequence_prefix() returns void as $$
begin
  -- 6 chars long pragma name, the sequence handler reads 3 chars behind
  -- the end of the string
  perform plpgsql_check_pragma('SEQUEN');
end;
$$ language plpgsql;
select * from plpgsql_check_function('pragma_sequence_prefix()');
WARNING:  unsupported pragma: SEQUEN
 plpgsql_check_function 
------------------------
(0 rows)

drop function pragma_sequence_prefix();
create function pragma_sequence_misdispatch() returns void as $$
begin
  perform plpgsql_check_pragma('SEQUENCE_FOO');
end;
$$ language plpgsql;
select * from plpgsql_check_function('pragma_sequence_misdispatch()');
WARNING:  unsupported pragma: SEQUENCE_FOO
 plpgsql_check_function 
------------------------
(0 rows)

drop function pragma_sequence_misdispatch();
create function profiled_function() returns int as $$
begin
  return 1;
end;
$$ language plpgsql;
set plpgsql_check.profiler to on;
begin;
select profiled_function();
 profiled_function 
-------------------
                 1
(1 row)

select plpgsql_profiler_reset_all();
 plpgsql_profiler_reset_all 
----------------------------
 
(1 row)

commit;
set plpgsql_check.profiler to off;
drop function profiled_function();
do $$
declare
  src text := '';
begin
  for i in 1..3000 loop
    src := src || 'perform 1;';
  end loop;

  execute format('create or replace function too_many_statements() returns void as $q$
begin
  if false then
    %s
  end if;
end $q$ language plpgsql', src);
end;
$$;
set plpgsql_check.profiler to off;
drop function too_many_statements();
create function dynamic_query_with_params(p int) returns void as $$
begin
  execute 'select 1 where $1 > 0' using p;
end;
$$ language plpgsql;
set plpgsql_check.profiler to on;
select dynamic_query_with_params(1);
 dynamic_query_with_params 
---------------------------
 
(1 row)

set plpgsql_check.profiler to off;
drop function dynamic_query_with_params(int);
create function case_over_record() returns int as $$
declare
  r record;
begin
  case r
    when null then return 1;
    else return 2;
  end case;
end;
$$ language plpgsql;
select * from plpgsql_check_function('case_over_record()');
 plpgsql_check_function 
------------------------
(0 rows)

drop function case_over_record();
create table dynsql_tab(a int);
create function dynamic_sql_forms(tname text) returns void as $$
declare
  query text;
begin
  execute 'select * from dynsql_tab';
  execute format('select * from %I', tname);
  query := 'select * from ' || quote_ident(tname);
  execute query;
end;
$$ language plpgsql;
select * from plpgsql_check_function('dynamic_sql_forms(text)',
                                     all_warnings => true);
                          plpgsql_check_function                           
---------------------------------------------------------------------------
 performance:00000:5:EXECUTE:immutable expression without parameters found
 Detail: the EXECUTE command is not necessary probably
 Hint: Don't use dynamic SQL when you can use static SQL.
(3 rows)

drop function dynamic_sql_forms(text);
drop table dynsql_tab;
create function uninitialized_is_mp(v text) returns void as $$
begin
  -- the %L placeholder makes the query non constant, so it is checked in a
  -- subtransaction, and the check fails because the table does not exist
  execute format('select * from missing_table where a = %L', v);
end;
$$ language plpgsql;
select * from plpgsql_check_function('uninitialized_is_mp(text)',
                                     all_warnings => true);
 plpgsql_check_function 
------------------------
(0 rows)

drop function uninitialized_is_mp(text);
create table traced_tab(a int);
insert into traced_tab values(1);
create or replace function traced_trg_func() returns trigger as $$
begin
  return old;
end;
$$ language plpgsql;
create trigger traced_trg before delete on traced_tab
  for each row execute procedure traced_trg_func();
set plpgsql_check.enable_tracer to on;
set plpgsql_check.tracer to on;
set plpgsql_check.tracer_test_mode to true;
delete from traced_tab;
NOTICE:  #0   ->> start of function traced_trg_func() (oid=0, tnl=1)
NOTICE:  #0       triggered by before row delete trigger
NOTICE:  #0       "old" => '(1)'
NOTICE:  #0   <<- end of function traced_trg_func (elapsed time=0.010 ms)
set plpgsql_check.tracer to off;
set plpgsql_check.enable_tracer to off;
drop table traced_tab cascade;
drop function traced_trg_func();
create function failing_assert() returns void as $$
declare
  r record;
begin
  select 1 as x into r;
  -- the reference to a record field creates a RECFIELD datum
  assert r.x = 1;
end;
$$ language plpgsql;
set plpgsql_check.enable_tracer to on;
set plpgsql_check.tracer to on;
set plpgsql_check.trace_assert to on;
set plpgsql_check.tracer_test_mode to true;
select failing_assert();
NOTICE:  #0   ->> start of function failing_assert() (oid=0, tnl=1)
NOTICE:  PLpgSQL assert expression (r.x = 1) on line 7 of failing_assert() is true
NOTICE:   "r"."x" => '1'
NOTICE:  #0   <<- end of function failing_assert (elapsed time=0.010 ms)
 failing_assert 
----------------
 
(1 row)

set plpgsql_check.trace_assert to off;
set plpgsql_check.tracer to off;
set plpgsql_check.enable_tracer to off;
drop function failing_assert();
create function dynamic_record_param() returns setof record as $$
begin
  -- the 12th USING argument is a record, so the tuple descriptor of the
  -- returned unpinned record is deduced from the param
  return query execute 'select $12'
                 using 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, row(1, 2);
end;
$$ language plpgsql;
select * from plpgsql_check_function('dynamic_record_param()');
 plpgsql_check_function 
------------------------
(0 rows)

drop function dynamic_record_param();
create function polymorphic_out_first(out b anyelement, a anyelement) as $$
begin
  b := a;
end;
$$ language plpgsql;
create function call_polymorphic_out_first() returns void as $$
declare
  arg record;
  res record;
begin
  select 1 as x, 2 as y into arg;
  res := polymorphic_out_first(arg);
  raise notice '%', res;
end;
$$ language plpgsql;
select * from plpgsql_check_function('call_polymorphic_out_first()');
 plpgsql_check_function 
------------------------
(0 rows)

drop function call_polymorphic_out_first();
drop function polymorphic_out_first(anyelement);
create function memsafety_profiled_fx() returns int as $$
begin
  return 1;
end;
$$ language plpgsql;
set plpgsql_check.profiler to on;
begin;
select memsafety_profiled_fx();
 memsafety_profiled_fx 
-----------------------
                     1
(1 row)

select plpgsql_profiler_reset_all();
 plpgsql_profiler_reset_all 
----------------------------
 
(1 row)

select memsafety_profiled_fx();
 memsafety_profiled_fx 
-----------------------
                     1
(1 row)

select plpgsql_profiler_reset_all();
 plpgsql_profiler_reset_all 
----------------------------
 
(1 row)

commit;
begin;
select memsafety_profiled_fx();
 memsafety_profiled_fx 
-----------------------
                     1
(1 row)

select plpgsql_profiler_reset_all();
 plpgsql_profiler_reset_all 
----------------------------
 
(1 row)

rollback;
set plpgsql_check.profiler to off;
drop function memsafety_profiled_fx();
create table memsafety_pt(a int);
create function memsafety_pragma_tokens() returns void as $$
declare r record;
begin
  -- "(", ")", ",", "[" and "]" are one character tokens
  perform plpgsql_check_pragma('table: memsafety_pt1(a int, b numeric(10,2), c int[])');
  perform plpgsql_check_pragma('table: memsafety_pt2(like memsafety_pt)');
  for r in execute 'select * from memsafety_pt1' loop
    raise notice '%', r.b;
  end loop;
  for r in execute 'select * from memsafety_pt2' loop
    raise notice '%', r.a;
  end loop;
end;
$$ language plpgsql;
select * from plpgsql_check_function('memsafety_pragma_tokens()');
 plpgsql_check_function 
------------------------
(0 rows)

drop function memsafety_pragma_tokens();
drop table memsafety_pt;
create function memsafety_format_pct() returns void as $$
begin
  raise notice '%', format('abc%');
end;
$$ language plpgsql;
select * from plpgsql_check_function('memsafety_format_pct()');
                  plpgsql_check_function                  
----------------------------------------------------------
 error:22023:3:RAISE:unterminated format() type specifier
 Query: format('abc%')
 --            ^
 Hint: For a single "%%" use "%%%%".
(4 rows)

drop function memsafety_format_pct();
create function memsafety_format_pct2() returns void as $$
begin
  raise notice '%', format('abc%', 1);
end;
$$ language plpgsql;
select * from plpgsql_check_function('memsafety_format_pct2()');
                  plpgsql_check_function                  
----------------------------------------------------------
 error:22023:3:RAISE:unterminated format() type specifier
 Query: format('abc%', 1)
 --            ^
 Hint: For a single "%%" use "%%%%".
(4 rows)

drop function memsafety_format_pct2();
create function memsafety_nextval_missing_rel() returns void as $$
begin
  perform nextval('4294967000'::regclass);
end;
$$ language plpgsql;
select * from plpgsql_check_function('memsafety_nextval_missing_rel()');
                plpgsql_check_function                
------------------------------------------------------
 error:42809:3:PERFORM:"4294967000" is not a sequence
 Query: SELECT nextval('4294967000'::regclass)
 --                    ^
(3 rows)

drop function memsafety_nextval_missing_rel();
create type memsafety_ct as (a int, b int);
create function memsafety_case_composite() returns void as $$
declare v memsafety_ct;
begin
  v := row(1,2)::memsafety_ct;
  case v
    when row(1,2)::memsafety_ct then raise notice 'one';
    else raise notice 'other';
  end case;
end;
$$ language plpgsql;
select * from plpgsql_check_function('memsafety_case_composite()');
 plpgsql_check_function 
------------------------
(0 rows)

select memsafety_case_composite();
NOTICE:  one
 memsafety_case_composite 
--------------------------
 
(1 row)

drop function memsafety_case_composite();
drop type memsafety_ct;
-- the constant is assigned in a branch, so it must not be used afterwards
create or replace function repro02_stale_const(flag bool) returns void as $$
declare
  s text;
begin
  if flag then
    s := 'select * from repro02_missing';
  end if;

  execute s;
end;
$$ language plpgsql;
select * from plpgsql_check_function('repro02_stale_const(bool)');
 plpgsql_check_function 
------------------------
(0 rows)

-- control: an unconditional assignment really is a constant and must be used
create or replace function repro02_live_const() returns void as $$
declare
  s text;
begin
  s := 'select * from repro02_missing';

  execute s;
end;
$$ language plpgsql;
select * from plpgsql_check_function('repro02_live_const()');
                     plpgsql_check_function                      
-----------------------------------------------------------------
 error:42P01:7:EXECUTE:relation "repro02_missing" does not exist
 Query: select * from repro02_missing
 --                   ^
(3 rows)

drop function repro02_stale_const(bool);
drop function repro02_live_const();
set plpgsql_check.enable_tracer to on;
set plpgsql_check.tracer to on;
set plpgsql_check.tracer_test_mode to true;
set plpgsql_check.tracer_verbosity to verbose;
set client_min_messages to notice;
-- the OUT parameters of a function are collected into an (unnamed) row datum,
-- and RETURN traces that datum
create or replace function repro03_out_params(out a int, out b text) as $$
begin
  a := 1;
  b := 'x';
  return;
end;
$$ language plpgsql;
select * from repro03_out_params();
NOTICE:  #0            ->> start of function repro03_out_params() (oid=0, tnl=1)
NOTICE:  #0.1       2    --> start of statement block (tnl=1)
NOTICE:  #0.2       3      --> start of assignment a := 1 (tnl=1)
NOTICE:  #0.2                  "a" => null
NOTICE:  #0.2              <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2                  "a" => '1'
NOTICE:  #0.3       4      --> start of assignment b := 'x' (tnl=1)
NOTICE:  #0.3                  "b" => null
NOTICE:  #0.3              <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.3                  "b" => 'x'
NOTICE:  #0.4       5      --> start of RETURN (tnl=1)
NOTICE:  #0.4                  "(unnamed row)" => '(1,x)'
NOTICE:  #0.4              <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0.1            <-- end of statement block (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function repro03_out_params (elapsed time=0.010 ms)
 a | b 
---+---
 1 | x
(1 row)

set plpgsql_check.tracer to off;
set plpgsql_check.enable_tracer to off;
drop function repro03_out_params();
create or replace function repro04_tracked_const() returns void as $$
declare
  str text;
  res int;
begin
  str := 'constant';

  execute 'select $40'
     into res
    using 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
          11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
          21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
          31, 32, 33, 34, 35, 36, 37, 38, 39, 40;

  raise notice '%, %', str, res;
end;
$$ language plpgsql;
select * from plpgsql_check_function('repro04_tracked_const()');
 plpgsql_check_function 
------------------------
(0 rows)

create or replace function repro04_tracked_const() returns void as $$
declare
  str text;
  res int;
begin
  str := 'constant';

  execute 'select $41'
     into res
    using 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
          11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
          21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
          31, 32, 33, 34, 35, 36, 37, 38, 39, 40;

  raise notice '%, %', str, res;
end;
$$ language plpgsql;
select * from plpgsql_check_function('repro04_tracked_const()');
             plpgsql_check_function              
-------------------------------------------------
 error:42P02:8:EXECUTE:there is no parameter $41
 Query: select $41
 --            ^
(3 rows)

-- (b) param_get_desc(): the tuple descriptor of the unpinned record returned by
--     the dynamic query is deduced from a high numbered parameter
create or replace function repro04_param_desc() returns setof record as $$
begin
  return query execute 'select $40'
                 using 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
                       11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
                       31, 32, 33, 34, 35, 36, 37, 38, 39, row(1, 2);
end;
$$ language plpgsql;
select * from plpgsql_check_function('repro04_param_desc()');
 plpgsql_check_function 
------------------------
(0 rows)

-- (c) plpgsql_check_assign_tupdesc_dno(): a query with no output column at all
create table repro04_zero_columns();
create or replace function repro04_empty_tupdesc() returns int as $$
declare
  v int;
begin
  select * from repro04_zero_columns into v;
  execute 'select * from repro04_zero_columns' into v;
  return v;
end;
$$ language plpgsql;
select * from plpgsql_check_function('repro04_empty_tupdesc()');
                        plpgsql_check_function                         
-----------------------------------------------------------------------
 warning:00000:5:SQL statement:too few attributes for target variables
 Query: select * from repro04_zero_columns
 --     ^
 Detail: There are more target variables than output columns in query.
 Hint: Check target variables in SELECT INTO statement.
 Context: at SQL statement to v variables
 warning:00000:6:EXECUTE:too few attributes for target variables
 Query: select * from repro04_zero_columns
 --     ^
 Detail: There are more target variables than output columns in query.
 Hint: Check target variables in SELECT INTO statement.
 Context: at EXECUTE to v variables
(12 rows)

drop function repro04_tracked_const();
drop function repro04_param_desc();
drop function repro04_empty_tupdesc();
drop table repro04_zero_columns;
set plpgsql_check.profiler to on;
set plpgsql_check.use_lxcache to on;
create or replace function repro07_ok() returns int as $$
begin
  return 1;
end;
$$ language plpgsql;
create or replace function repro07_err() returns int as $$
begin
  raise exception 'boom';
end;
$$ language plpgsql;
-- control: an aborted function with no lxcache from an earlier call is fine
begin;
  select repro07_err();
ERROR:  boom
CONTEXT:  PL/pgSQL function repro07_err() line 3 at RAISE
commit;
-- the successful call creates the lxcache, the failing one leaves an execution
-- context behind, and the cleanup of that context re-enters get_lxcache()
begin;
  select repro07_ok();
 repro07_ok 
------------
          1
(1 row)

  select repro07_err();
ERROR:  boom
CONTEXT:  PL/pgSQL function repro07_err() line 3 at RAISE
commit;
select 'survived' as result;
  result  
----------
 survived
(1 row)

-- the same happens on an explicit ROLLBACK
begin;
  select repro07_ok();
 repro07_ok 
------------
          1
(1 row)

  select repro07_err();
ERROR:  boom
CONTEXT:  PL/pgSQL function repro07_err() line 3 at RAISE
rollback;
select 'survived' as result;
  result  
----------
 survived
(1 row)

set plpgsql_check.profiler to off;
drop function if exists repro07_ok();
drop function if exists repro07_err();
CREATE SCHEMA IF NOT EXISTS repro01;
SET search_path = repro01, public;
CREATE TABLE t_b (a int);
-- The EXECUTE records t_b into cstate_dynsql.rel_oids, which is then thrown
-- away.  The static PERFORM records the very same OID again, this time into
-- the caller's cstate->rel_oids.
CREATE FUNCTION f_dup() RETURNS void AS $$
BEGIN
  EXECUTE 'select * from repro01.t_b';
  PERFORM * FROM repro01.t_b;
END
$$ LANGUAGE plpgsql;
\echo '### Finding 1: duplicate RELATION dependency (expect 1 row, bug yields 2)'
### Finding 1: duplicate RELATION dependency (expect 1 row, bug yields 2)
SELECT type, schema, name FROM plpgsql_show_dependency_tb('repro01.f_dup()')
ORDER BY type, name;
   type   | schema  | name 
----------+---------+------
 RELATION | repro01 | t_b
(1 row)

\echo '### Same query, counted:'
### Same query, counted:
SELECT count(*) AS relation_rows_for_t_b
  FROM plpgsql_show_dependency_tb('repro01.f_dup()')
 WHERE type = 'RELATION' AND name = 't_b';
 relation_rows_for_t_b 
-----------------------
                     1
(1 row)

-- Control: the purely static equivalent reports the relation exactly once.
CREATE FUNCTION f_static() RETURNS void AS $$
BEGIN
  PERFORM * FROM repro01.t_b;
  PERFORM * FROM repro01.t_b;
END
$$ LANGUAGE plpgsql;
\echo '### Control: static-only function reports t_b once'
### Control: static-only function reports t_b once
SELECT count(*) AS relation_rows_for_t_b
  FROM plpgsql_show_dependency_tb('repro01.f_static()')
 WHERE type = 'RELATION' AND name = 't_b';
 relation_rows_for_t_b 
-----------------------
                     1
(1 row)

DROP SCHEMA repro01 CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table t_b
drop cascades to function f_dup()
drop cascades to function f_static()
CREATE SCHEMA IF NOT EXISTS repro04;
SET search_path = repro04, public;
SET plpgsql_check.tracer_test_mode = true;
SET plpgsql_check.tracer = on;
SET plpgsql_check.tracer_verbosity = verbose;
SET client_min_messages = error;   -- silence the trace output itself
-- (1) exception at maximum depth, EMPTY handler: nothing inside the handler
--     calls stmt_beg, so the stack is only unwound by stmt_end of the block.
CREATE FUNCTION f_empty_handler() RETURNS int AS $$
DECLARE x int := 0;
BEGIN
  FOR i IN 1..3 LOOP
    BEGIN
      RAISE EXCEPTION 'boom';
    EXCEPTION WHEN others THEN
    END;
  END LOOP;
  RETURN x;
END $$ LANGUAGE plpgsql;
-- (2) nested handlers, each re-raising: unwinds several levels per stmt_beg.
CREATE FUNCTION f_nested_reraise() RETURNS int AS $$
BEGIN
  BEGIN
    BEGIN
      RAISE EXCEPTION 'inner';
    EXCEPTION WHEN others THEN
      RAISE EXCEPTION 'middle';
    END;
  EXCEPTION WHEN others THEN
    PERFORM 1;
  END;
  RETURN 1;
END $$ LANGUAGE plpgsql;
-- (3) exception thrown from deep inside loops and caught at the very top.
CREATE FUNCTION f_deep_escape() RETURNS int AS $$
DECLARE j int;
BEGIN
  BEGIN
    FOR i IN 1..2 LOOP
      FOREACH j IN ARRAY ARRAY[1,2] LOOP
        WHILE true LOOP
          IF j = 2 THEN
            RAISE EXCEPTION 'deep';
          END IF;
          EXIT;
        END LOOP;
      END LOOP;
    END LOOP;
  EXCEPTION WHEN others THEN
    RETURN -1;
  END;
  RETURN 0;
END $$ LANGUAGE plpgsql;
-- (4) handler whose FIRST statement is itself a block (repair path must match
--     the block's parent, not the failing statement's parent).
CREATE FUNCTION f_handler_block() RETURNS int AS $$
BEGIN
  BEGIN
    BEGIN
      RAISE EXCEPTION 'x';
    EXCEPTION WHEN others THEN
      BEGIN
        PERFORM 1;
      END;
    END;
  END;
  RETURN 1;
END $$ LANGUAGE plpgsql;
-- (5) repeated entry/exit so any leaked stack slot accumulates across calls.
CREATE FUNCTION f_repeat() RETURNS int AS $$
DECLARE n int := 0;
BEGIN
  FOR i IN 1..50 LOOP
    BEGIN
      BEGIN
        RAISE EXCEPTION 'e%', i;
      EXCEPTION WHEN others THEN
      END;
      n := n + 1;
    EXCEPTION WHEN others THEN
      n := n - 1;
    END;
  END LOOP;
  RETURN n;
END $$ LANGUAGE plpgsql;
-- (6) exception crossing a function boundary (separate plugin_info per estate).
CREATE FUNCTION f_callee() RETURNS int AS $$
BEGIN
  BEGIN
    RAISE EXCEPTION 'from callee';
  END;
END $$ LANGUAGE plpgsql;
CREATE FUNCTION f_caller() RETURNS int AS $$
BEGIN
  BEGIN
    FOR i IN 1..2 LOOP
      PERFORM f_callee();
    END LOOP;
  EXCEPTION WHEN others THEN
    RETURN -1;
  END;
  RETURN 0;
END $$ LANGUAGE plpgsql;
RESET client_min_messages;
\echo '### Driving stmt_beg/stmt_end imbalance patterns under the tracer'
### Driving stmt_beg/stmt_end imbalance patterns under the tracer
SET client_min_messages = error;
SELECT f_empty_handler();
 f_empty_handler 
-----------------
               0
(1 row)

SELECT f_nested_reraise();
 f_nested_reraise 
------------------
                1
(1 row)

SELECT f_deep_escape();
 f_deep_escape 
---------------
            -1
(1 row)

SELECT f_handler_block();
 f_handler_block 
-----------------
               1
(1 row)

SELECT f_repeat();
 f_repeat 
----------
       50
(1 row)

SELECT f_caller();
 f_caller 
----------
       -1
(1 row)

-- run them all again, now with the profiler also attached (two active plugins,
-- which additionally exercises the duplicated palloc in func_setup)
SET plpgsql_check.profiler = on;
SELECT f_empty_handler();
 f_empty_handler 
-----------------
               0
(1 row)

SELECT f_nested_reraise();
 f_nested_reraise 
------------------
                1
(1 row)

SELECT f_deep_escape();
 f_deep_escape 
---------------
            -1
(1 row)

SELECT f_handler_block();
 f_handler_block 
-----------------
               1
(1 row)

SELECT f_repeat();
 f_repeat 
----------
       50
(1 row)

SELECT f_caller();
 f_caller 
----------
       -1
(1 row)

RESET client_min_messages;
\echo '### Survived: no assertion failure and the backend is still alive'
### Survived: no assertion failure and the backend is still alive
SELECT 'still alive' AS status;
   status    
-------------
 still alive
(1 row)

DROP SCHEMA repro04 CASCADE;
NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to function f_empty_handler()
drop cascades to function f_nested_reraise()
drop cascades to function f_deep_escape()
drop cascades to function f_handler_block()
drop cascades to function f_repeat()
drop cascades to function f_callee()
drop cascades to function f_caller()
SELECT format('%2$s', 'a');
ERROR:  too few arguments for format()
CREATE OR REPLACE FUNCTION repro05_a() RETURNS void AS $$
BEGIN
    RAISE NOTICE '%', format('%2$s', 'a');
END
$$ LANGUAGE plpgsql;
-- buggy: reports nothing
-- correct: error:22023:...:RAISE:too few arguments for format()
SELECT * FROM plpgsql_check_function('repro05_a()');
               plpgsql_check_function               
----------------------------------------------------
 error:22023:3:RAISE:too few arguments for format()
 Query: format('%2$s', 'a')
 --            ^
(3 rows)

-- for comparison, the same mistake without an explicit position IS reported
CREATE OR REPLACE FUNCTION repro05_a2() RETURNS void AS $$
BEGIN
    RAISE NOTICE '%', format('%s %s', 'a');
END
$$ LANGUAGE plpgsql;
SELECT * FROM plpgsql_check_function('repro05_a2()');
               plpgsql_check_function               
----------------------------------------------------
 error:22023:3:RAISE:too few arguments for format()
 Query: format('%s %s', 'a')
 --            ^
(3 rows)

----------------------------------------------------------------------
-- part B: missed SQL-injection warning
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION repro05_b(p text) RETURNS void AS $$
BEGIN
    EXECUTE format('SELECT %1$s', p);
END
$$ LANGUAGE plpgsql;
-- buggy: reports nothing
-- correct: security:00000:...:EXECUTE:text type variable is not sanitized
SELECT * FROM plpgsql_check_function('repro05_b(text)', security_warnings => true);
                           plpgsql_check_function                            
-----------------------------------------------------------------------------
 security:00000:3:EXECUTE:text type variable is not sanitized
 Query: format('SELECT %1$s', p)
 --                           ^
 Detail: The EXECUTE expression is SQL injection vulnerable.
 Hint: Use quote_ident, quote_literal or format function to secure variable.
(5 rows)

-- for comparison, the same code without an explicit position IS reported
CREATE OR REPLACE FUNCTION repro05_b2(p text) RETURNS void AS $$
BEGIN
    EXECUTE format('SELECT %s', p);
END
$$ LANGUAGE plpgsql;
SELECT * FROM plpgsql_check_function('repro05_b2(text)', security_warnings => true);
                           plpgsql_check_function                            
-----------------------------------------------------------------------------
 security:00000:3:EXECUTE:text type variable is not sanitized
 Query: format('SELECT %s', p)
 --                         ^
 Detail: The EXECUTE expression is SQL injection vulnerable.
 Hint: Use quote_ident, quote_literal or format function to secure variable.
(5 rows)

DROP SEQUENCE IF EXISTS repro02_seq;
NOTICE:  sequence "repro02_seq" does not exist, skipping
CREATE SEQUENCE repro02_seq;
-- 1st call returns a valid query, every later call returns NULL
CREATE OR REPLACE FUNCTION repro02_gen() RETURNS text AS $$
BEGIN
    IF nextval('repro02_seq') = 1 THEN
        RETURN 'SELECT 1';
    ELSE
        RETURN NULL;
    END IF;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION repro02_run() RETURNS void AS $$
BEGIN
    EXECUTE repro02_gen();
END
$$ LANGUAGE plpgsql;
-- PGC_USERSET
SET plpgsql_check.profiler = on;
-- boom
SELECT repro02_run();
 repro02_run 
-------------
 
(1 row)

SET plpgsql_check.profiler = off;
DROP SEQUENCE IF EXISTS repro03_seq;
NOTICE:  sequence "repro03_seq" does not exist, skipping
CREATE SEQUENCE repro03_seq;
----------------------------------------------------------------------
-- part A: the side effect of the query-text expression happens twice
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION repro03_gen() RETURNS text AS $$
BEGIN
    PERFORM nextval('repro03_seq');
    RETURN 'SELECT 1';
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION repro03_run() RETURNS void AS $$
BEGIN
    EXECUTE repro03_gen();
END
$$ LANGUAGE plpgsql;
SET plpgsql_check.profiler = off;
SELECT setval('repro03_seq', 1, false);
 setval 
--------
      1
(1 row)

SELECT repro03_run();
 repro03_run 
-------------
 
(1 row)

-- correct: 1
SELECT currval('repro03_seq') AS "nextval calls with profiler off";
 nextval calls with profiler off 
---------------------------------
                               1
(1 row)

SET plpgsql_check.profiler = on;
SELECT setval('repro03_seq', 1, false);
 setval 
--------
      1
(1 row)

SELECT repro03_run();
 repro03_run 
-------------
 
(1 row)

-- buggy: 2 - the profiler evaluated repro03_gen() a second time
SELECT currval('repro03_seq') AS "nextval calls with profiler on";
 nextval calls with profiler on 
--------------------------------
                              1
(1 row)