plpgsql_check_active-11.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;