plpgsql_check_active-14.out

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);