LOAD 'plpgsql';
CREATE EXTENSION  IF NOT EXISTS plpgsql_check;
NOTICE:  extension "plpgsql_check" already exists, skipping
set client_min_messages to notice;
create or replace procedure proc(a int)
as $$
begin
end;
$$ language plpgsql;
call proc(10);
select * from plpgsql_check_function('proc(int)');
          plpgsql_check_function          
------------------------------------------
 warning extra:00000:unused parameter "a"
(1 row)

create or replace procedure testproc()
as $$
begin
  call proc(10);
end;
$$ language plpgsql;
call testproc();
select * from plpgsql_check_function('testproc()');
 plpgsql_check_function 
------------------------
(0 rows)

-- should to fail
create or replace procedure testproc()
as $$
begin
  call proc((select count(*) from pg_class));
end;
$$ language plpgsql;
call testproc();
ERROR:  cannot use subquery in CALL argument
LINE 1: call proc((select count(*) from pg_class))
                  ^
QUERY:  call proc((select count(*) from pg_class))
CONTEXT:  PL/pgSQL function testproc() line 3 at CALL
select * from plpgsql_check_function('testproc()');
                 plpgsql_check_function                  
---------------------------------------------------------
 error:0A000:3:CALL:cannot use subquery in CALL argument
 Query: call proc((select count(*) from pg_class))
 --               ^
(3 rows)

drop procedure proc(int);
create procedure proc(in a int, inout b int, in c int)
as $$
begin
end;
$$ language plpgsql;
select * from plpgsql_check_function('proc(int,int, int)');
             plpgsql_check_function              
-------------------------------------------------
 warning extra:00000:unused parameter "a"
 warning extra:00000:unused parameter "b"
 warning extra:00000:unused parameter "c"
 warning extra:00000:unmodified OUT variable "b"
(4 rows)

create or replace procedure proc(in a int, inout b int, in c int)
as $$
begin
  b := a + c;
end;
$$ language plpgsql;
select * from plpgsql_check_function('proc(int,int, int)');
 plpgsql_check_function 
------------------------
(0 rows)

create or replace procedure testproc()
as $$
declare r int;
begin
  call proc(10, r, 20);
end;
$$ language plpgsql;
call testproc();
select * from plpgsql_check_function('testproc()');
 plpgsql_check_function 
------------------------
(0 rows)

-- should to fail
create or replace procedure testproc()
as $$
declare r int;
begin
  call proc(10, r + 10, 20);
end;
$$ language plpgsql;
call testproc();
ERROR:  procedure parameter "b" is an output parameter but corresponding argument is not writable
CONTEXT:  PL/pgSQL function testproc() line 4 at CALL
select * from plpgsql_check_function('testproc()');
                                            plpgsql_check_function                                            
--------------------------------------------------------------------------------------------------------------
 error:42601:4:CALL:procedure parameter "b" is an output parameter but corresponding argument is not writable
(1 row)

create or replace procedure testproc(inout r int)
as $$
begin
  call proc(10, r, 20);
end;
$$ language plpgsql;
call testproc(10);
 r  
----
 30
(1 row)

select * from plpgsql_check_function('testproc(int)');
 plpgsql_check_function 
------------------------
(0 rows)

drop procedure testproc(int);
-- should to raise warnings
create or replace procedure testproc2(in p1 int, inout p2 int, in p3 int, inout p4 int)
as $$
begin
  raise notice '% %', p1, p3;
end;
$$ language plpgsql;
select * from plpgsql_check_function('testproc2');
              plpgsql_check_function              
--------------------------------------------------
 warning extra:00000:unused parameter "p2"
 warning extra:00000:unused parameter "p4"
 warning extra:00000:unmodified OUT variable "p2"
 warning extra:00000:unmodified OUT variable "p4"
(4 rows)

drop procedure testproc2;
-- should be ok
create or replace procedure testproc3(in p1 int, inout p2 int, in p3 int, inout p4 int)
as $$
begin
  p2 := p1;
  p4 := p3;
end;
$$ language plpgsql;
select * from plpgsql_check_function('testproc3');
 plpgsql_check_function 
------------------------
(0 rows)

drop procedure testproc3;
/*
 * Test pragma
 */
create or replace function test_pragma()
 returns void
 language plpgsql
as $$
declare r record;
begin
  perform plpgsql_check_pragma('disable:check');
  raise notice '%', r.y;
  perform plpgsql_check_pragma('enable:check');
  select 10 as a, 20 as b into r;
  raise notice '%', r.a;
  raise notice '%', r.x;
end;
$$;
select * from plpgsql_check_function('test_pragma');
             plpgsql_check_function              
-------------------------------------------------
 error:42703:9:RAISE:record "r" has no field "x"
 Context: SQL expression "r.x"
(2 rows)

create or replace function test_pragma()
 returns void
 language plpgsql
as $$
declare r record;
begin
  if false then
    -- check is disabled just for if body
    perform plpgsql_check_pragma('disable:check');
    raise notice '%', r.y;
  end if;
  select 10 as a, 20 as b into r;
  raise notice '%', r.a;
  raise notice '%', r.x;
end;
$$;
select * from plpgsql_check_function('test_pragma');
              plpgsql_check_function              
--------------------------------------------------
 error:42703:11:RAISE:record "r" has no field "x"
 Context: SQL expression "r.x"
(2 rows)

drop function test_pragma();
create or replace function nested_trace_test(a int)
returns int as $$
begin
  return a + 1;
end;
$$ language plpgsql;
create or replace function trace_test(b int)
returns int as $$
declare r int default 0;
begin
  for i in 1..b
  loop
    r := nested_trace_test(r);
  end loop;
  return r;
end;
$$ language plpgsql;
select trace_test(3);
 trace_test 
------------
          3
(1 row)

set plpgsql_check.enable_tracer to on;
set plpgsql_check.tracer to on;
set plpgsql_check.tracer_test_mode = true;
select trace_test(3);
NOTICE:  #0   ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0       "b" => '3'
NOTICE:  #1     ->> start of function nested_trace_test(integer) (oid=0)
NOTICE:  #1          call by trace_test(integer) line 6 at assignment
NOTICE:  #1         "a" => '0'
NOTICE:  #1     <<- end of function nested_trace_test (elapsed time=0.010 ms)
NOTICE:  #1     ->> start of function nested_trace_test(integer) (oid=0)
NOTICE:  #1          call by trace_test(integer) line 6 at assignment
NOTICE:  #1         "a" => '1'
NOTICE:  #1     <<- end of function nested_trace_test (elapsed time=0.010 ms)
NOTICE:  #1     ->> start of function nested_trace_test(integer) (oid=0)
NOTICE:  #1          call by trace_test(integer) line 6 at assignment
NOTICE:  #1         "a" => '2'
NOTICE:  #1     <<- end of function nested_trace_test (elapsed time=0.010 ms)
NOTICE:  #0   <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
          3
(1 row)

set plpgsql_check.tracer_verbosity TO verbose;
select trace_test(3);
NOTICE:  #0            ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0                "b" => '3'
NOTICE:  #0.1       4  --> start of FOR with integer loop variable
NOTICE:  #0.2       6  --> start of assignment r := nested_trace_test(r)
NOTICE:  #0.2              "r" => '0'
NOTICE:  #1              ->> start of function nested_trace_test(integer) (oid=0)
NOTICE:  #1                   call by trace_test(integer) line 6 at assignment
NOTICE:  #1                  "a" => '0'
NOTICE:  #1.1       3    --> start of RETURN (expr='a + 1')
NOTICE:  #1.1                "a" => '0'
NOTICE:  #1.1            <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #1              <<- end of function nested_trace_test (elapsed time=0.010 ms)
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '1'
NOTICE:  #0.2       6  --> start of assignment r := nested_trace_test(r)
NOTICE:  #0.2              "r" => '1'
NOTICE:  #1              ->> start of function nested_trace_test(integer) (oid=0)
NOTICE:  #1                   call by trace_test(integer) line 6 at assignment
NOTICE:  #1                  "a" => '1'
NOTICE:  #1.1       3    --> start of RETURN (expr='a + 1')
NOTICE:  #1.1                "a" => '1'
NOTICE:  #1.1            <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #1              <<- end of function nested_trace_test (elapsed time=0.010 ms)
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '2'
NOTICE:  #0.2       6  --> start of assignment r := nested_trace_test(r)
NOTICE:  #0.2              "r" => '2'
NOTICE:  #1              ->> start of function nested_trace_test(integer) (oid=0)
NOTICE:  #1                   call by trace_test(integer) line 6 at assignment
NOTICE:  #1                  "a" => '2'
NOTICE:  #1.1       3    --> start of RETURN (expr='a + 1')
NOTICE:  #1.1                "a" => '2'
NOTICE:  #1.1            <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #1              <<- end of function nested_trace_test (elapsed time=0.010 ms)
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '3'
NOTICE:  #0.1          <-- end of FOR with integer loop variable (elapsed time=0.010 ms)
NOTICE:  #0.3       8  --> start of RETURN
NOTICE:  #0.3              "r" => '3'
NOTICE:  #0.3          <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
          3
(1 row)

create or replace function trace_test(b int)
returns int as $$
declare r int default 0;
begin
  for i in 1..b
  loop
    perform plpgsql_check_pragma('disable:tracer');
    r := nested_trace_test(r);
  end loop;
  return r;
end;
$$ language plpgsql;
select trace_test(3);
NOTICE:  #0            ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0                "b" => '3'
NOTICE:  #0.1       4  --> start of FOR with integer loop variable
NOTICE:  #0.2       6  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.2       6  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.2       6  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.1          <-- end of FOR with integer loop variable (elapsed time=0.010 ms)
NOTICE:  #0.4       9  --> start of RETURN
NOTICE:  #0.4              "r" => '3'
NOTICE:  #0.4          <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
          3
(1 row)

create or replace function nested_trace_test(a int)
returns int as $$
begin
  perform plpgsql_check_pragma('enable:tracer');
  return a + 1;
end;
$$ language plpgsql;
select trace_test(3);
NOTICE:  #0            ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0                "b" => '3'
NOTICE:  #0.1       4  --> start of FOR with integer loop variable
NOTICE:  #0.2       6  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #1.2       4    --> start of RETURN (expr='a + 1')
NOTICE:  #1.2                "a" => '0'
NOTICE:  #1.2            <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0.2       6  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #1.2       4    --> start of RETURN (expr='a + 1')
NOTICE:  #1.2                "a" => '1'
NOTICE:  #1.2            <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0.2       6  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #1.2       4    --> start of RETURN (expr='a + 1')
NOTICE:  #1.2                "a" => '2'
NOTICE:  #1.2            <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0.1          <-- end of FOR with integer loop variable (elapsed time=0.010 ms)
NOTICE:  #0.4       9  --> start of RETURN
NOTICE:  #0.4              "r" => '3'
NOTICE:  #0.4          <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
          3
(1 row)

drop function trace_test(int);
drop function nested_trace_test(int);
create or replace function trace_test(int)
returns int as $$
declare r int default 0;
begin
  for i in 1..$1 loop
    r := r + 1;
  end loop;
  r := r + 10;
  return r;
end;
$$ language plpgsql;
select trace_test(4);
NOTICE:  #0            ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0                "$1" => '4'
NOTICE:  #0.1       4  --> start of FOR with integer loop variable
NOTICE:  #0.2       5  --> start of assignment r := r + 1
NOTICE:  #0.2              "r" => '0'
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '1'
NOTICE:  #0.2       5  --> start of assignment r := r + 1
NOTICE:  #0.2              "r" => '1'
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '2'
NOTICE:  #0.2       5  --> start of assignment r := r + 1
NOTICE:  #0.2              "r" => '2'
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '3'
NOTICE:  #0.2       5  --> start of assignment r := r + 1
NOTICE:  #0.2              "r" => '3'
NOTICE:  #0.2          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.2              "r" => '4'
NOTICE:  #0.1          <-- end of FOR with integer loop variable (elapsed time=0.010 ms)
NOTICE:  #0.3       7  --> start of assignment r := r + 10
NOTICE:  #0.3              "r" => '4'
NOTICE:  #0.3          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.3              "r" => '14'
NOTICE:  #0.4       8  --> start of RETURN
NOTICE:  #0.4              "r" => '14'
NOTICE:  #0.4          <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
         14
(1 row)

create or replace function trace_test(int)
returns int as $$
declare r int default 0;
begin
  for i in 1..$1 loop
    perform plpgsql_check_pragma('disable:tracer');
    r := r + 1;
  end loop;
  r := r + 10;
  return r;
end;
$$ language plpgsql;
select trace_test(4);
NOTICE:  #0            ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0                "$1" => '4'
NOTICE:  #0.1       4  --> start of FOR with integer loop variable
NOTICE:  #0.2       5  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.2       5  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.2       5  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.2       5  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.2          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.1          <-- end of FOR with integer loop variable (elapsed time=0.010 ms)
NOTICE:  #0.4       8  --> start of assignment r := r + 10
NOTICE:  #0.4              "r" => '4'
NOTICE:  #0.4          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.4              "r" => '14'
NOTICE:  #0.5       9  --> start of RETURN
NOTICE:  #0.5              "r" => '14'
NOTICE:  #0.5          <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
         14
(1 row)

create or replace function trace_test(int)
returns int as $$
declare r int default 0;
begin
  perform plpgsql_check_pragma('disable:tracer');

  for i in 1..$1 loop
    r := r + 1;
  end loop;

  perform plpgsql_check_pragma('enable:tracer');

  r := r + 10;
  return r;
end;
$$ language plpgsql;
select trace_test(4);
NOTICE:  #0            ->> start of function trace_test(integer) (oid=0)
NOTICE:  #0                "$1" => '4'
NOTICE:  #0.1       4  --> start of perform plpgsql_check_pragma('disable: ..
NOTICE:  #0.1          <-- end of PERFORM (elapsed time=0.010 ms)
NOTICE:  #0.5      12  --> start of assignment r := r + 10
NOTICE:  #0.5              "r" => '4'
NOTICE:  #0.5          <-- end of assignment (elapsed time=0.010 ms)
NOTICE:  #0.5              "r" => '14'
NOTICE:  #0.6      13  --> start of RETURN
NOTICE:  #0.6              "r" => '14'
NOTICE:  #0.6          <-- end of RETURN (elapsed time=0.010 ms)
NOTICE:  #0            <<- end of function trace_test (elapsed time=0.010 ms)
 trace_test 
------------
         14
(1 row)

drop function trace_test(int);
create or replace function fxtest()
returns void as $$
declare
  v_sqlstate text;
  v_message text;
  v_context text;
begin
  get stacked diagnostics
    v_sqlstate = returned_sqlstate,
    v_message = message_text,
    v_context = pg_exception_context;
end;
$$ language plpgsql;
select * from plpgsql_check_function('fxtest');
                                          plpgsql_check_function                                           
-----------------------------------------------------------------------------------------------------------
 error:0Z002:7:GET STACKED DIAGNOSTICS:GET STACKED DIAGNOSTICS cannot be used outside an exception handler
(1 row)

drop function fxtest();
create or replace procedure prtest()
as $$
begin
  commit;
end;
$$ language plpgsql;
select * from plpgsql_check_function('prtest'); --ok
 plpgsql_check_function 
------------------------
(0 rows)

create or replace procedure prtest()
as $$
begin
  begin
    begin
      commit;
    end;
  end;
exception when others then
  raise;
end;
$$ language plpgsql;
select * from plpgsql_check_function('prtest'); --error
                       plpgsql_check_function                        
---------------------------------------------------------------------
 error:2D000:5:COMMIT:cannot commit while a subtransaction is active
(1 row)

create or replace procedure prtest()
as $$
begin
  raise exception 'error';
exception when others then
  begin
    begin
      commit;
    end;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('prtest'); --ok
 plpgsql_check_function 
------------------------
(0 rows)

drop procedure prtest();
create function return_constant_refcursor() returns refcursor as $$
declare
    rc constant refcursor;
begin
    open rc for select a from rc_test;
    return rc;
end
$$ language plpgsql;
create table rc_test(a int);
select * from plpgsql_check_function('return_constant_refcursor');
                plpgsql_check_function                 
-------------------------------------------------------
 error:22005:5:OPEN:variable "rc" is declared CONSTANT
(1 row)

drop table rc_test;
drop function return_constant_refcursor();
create procedure p1(a int, out b int)
as $$
begin
  b := a + 10;
end;
$$ language plpgsql;
create function f1()
returns void as $$
declare b constant int;
begin
  call p1(10, b);
end;
$$ language plpgsql;
select * from plpgsql_check_function('f1');
                plpgsql_check_function                
------------------------------------------------------
 error:22005:4:CALL:variable "b" is declared CONSTANT
(1 row)

drop function f1();
drop procedure p1(int, int);
create or replace function f1()
returns int as $$
declare c constant int default 100;
begin
  return c;
end;
$$ language plpgsql;
-- should be ok
select * from plpgsql_check_function('f1');
 plpgsql_check_function 
------------------------
(0 rows)

drop function f1();
-- do not raise false warning
create or replace function test_function()
returns text as $$
declare s text;
begin
  get diagnostics s = PG_CONTEXT;
  return s;
end;
$$ language plpgsql;
create or replace procedure test_procedure()
as $$
begin
  null;
end;
$$ language plpgsql;
-- should be without any warnings
select * from plpgsql_check_function('test_function', performance_warnings=>true);
 plpgsql_check_function 
------------------------
(0 rows)

select * from plpgsql_check_function('test_procedure', performance_warnings=>true);
 plpgsql_check_function 
------------------------
(0 rows)

drop function test_function();
drop procedure test_procedure();
-- detect dependecy in CALL statement
create or replace function fx1_dep(int)
returns int as $$
begin
  return $1;
end;
$$ language plpgsql;
create or replace procedure px1_dep(int)
as $$
begin
end;
$$ language plpgsql;
create or replace function test_function()
returns void as $$
begin
  call px1_dep(fx1_dep(10));
end;
$$ language plpgsql;
select type, schema, name, params from plpgsql_show_dependency_tb('test_function');
   type    | schema |  name   |  params   
-----------+--------+---------+-----------
 FUNCTION  | public | fx1_dep | (integer)
 PROCEDURE | public | px1_dep | (integer)
(2 rows)

drop function test_function();
drop procedure px1_dep();
ERROR:  procedure px1_dep() does not exist
drop function fx1_dep(int);