set client_min_messages to warning;
create extension if not exists plpgsql_check;
set client_min_messages to notice;
--
-- WHILE statement
--
-- the condition is checked like a scalar boolean expression
create function sw_f1(n int)
returns int as $$
declare i int := 0;
begin
  while i < n loop
    i := i + 1;
  end loop;
  return i;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f1');
 plpgsql_check_function 
------------------------
(0 rows)

-- a non boolean condition is reported
create function sw_f2(n int)
returns int as $$
declare i int := 0;
begin
  while i loop
    i := i + 1;
  end loop;
  return i;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f2');
                                plpgsql_check_function                                
--------------------------------------------------------------------------------------
 warning:42804:4:WHILE:target type is different type than source type
 Detail: cast "integer" value to "boolean" type
 Hint: The input expression type does not have an assignment cast to the target type.
 Context: at WHILE
 warning extra:00000:unused parameter "n"
(5 rows)

-- the body of a loop is not necessarily executed, so a RETURN inside
-- the loop does not close the execution path
create function sw_f3(n int)
returns int as $$
begin
  while n > 0 loop
    return n;
  end loop;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f3');
                       plpgsql_check_function                       
--------------------------------------------------------------------
 warning extra:2F005:control reached end of function without RETURN
(1 row)

-- an unconditional loop is closed by the RETURN inside its body
create function sw_f4(n int)
returns int as $$
begin
  loop
    return n;
  end loop;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f4');
 plpgsql_check_function 
------------------------
(0 rows)

-- the STEP expression of a numeric FOR loop is checked too
create function sw_f4b(n int)
returns int as $$
declare i int := 0;
begin
  for j in 1..n by 2 loop
    i := i + j;
  end loop;
  return i;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f4b');
 plpgsql_check_function 
------------------------
(0 rows)

--
-- names of the local variables
--
-- a reserved keyword used as a variable name, a local variable shadowing
-- a parameter and a local variable shadowing an outer local variable
create function sw_f4c(shadowed_par int)
returns int as $$
declare
  "table" int := 1;
  shadowed_var int := 2;
begin
  declare
    shadowed_par int := 3;
    shadowed_var int := 4;
  begin
    return "table" + shadowed_par + shadowed_var;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f4c');
                                       plpgsql_check_function                                        
-----------------------------------------------------------------------------------------------------
 warning:00000:5:statement block:name of variable "table" is reserved keyword
 Detail: The reserved keyword was used as variable name.
 warning:00000:9:statement block:parameter "shadowed_par" is shadowed
 Detail: Local variable shadows function parameter.
 warning extra:00000:9:statement block:variable "shadowed_var" shadows a previously defined variable
 Hint: SET plpgsql.extra_warnings TO 'shadowed_variables'
 warning extra:00000:4:DECLARE:never read variable "shadowed_var"
 warning extra:00000:unused parameter "shadowed_par"
(8 rows)

--
-- CASE statement
--
-- when the CASE has no ELSE part, the execution path is closed only
-- possibly, although every WHEN part returns
create function sw_f5(n int)
returns int as $$
begin
  case n
    when 1 then return 10;
    when 2 then return 20;
  end case;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f5');
                       plpgsql_check_function                       
--------------------------------------------------------------------
 warning extra:2F005:control reached end of function without RETURN
(1 row)

-- with the ELSE part the path is closed
create function sw_f6(n int)
returns int as $$
begin
  case n
    when 1 then return 10;
    else return 20;
  end case;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f6');
 plpgsql_check_function 
------------------------
(0 rows)

--
-- IF statement with ELSIF parts
--
-- every ELSIF condition is checked separately
create function sw_f7(n int)
returns int as $$
begin
  if n = 1 then
    return 10;
  elsif n then
    return 20;
  elsif n = 3 then
    return 30;
  else
    return 40;
  end if;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f7');
                                plpgsql_check_function                                
--------------------------------------------------------------------------------------
 warning:42804:4:RETURN:target type is different type than source type
 Detail: cast "integer" value to "boolean" type
 Hint: The input expression type does not have an assignment cast to the target type.
 Context: at RETURN
(4 rows)

--
-- EXIT and CONTINUE statements
--
-- EXIT can leave a labeled statement block, CONTINUE needs a labeled loop
create function sw_f8(n int)
returns int as $$
declare i int := 0;
begin
  <<outer_block>>
  begin
    <<outer_loop>>
    for j in 1..n loop
      if j = 2 then
        continue outer_loop;
      end if;
      if j = 3 then
        exit outer_block;
      end if;
      i := i + j;
    end loop;
  end;
  return i;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f8');
 plpgsql_check_function 
------------------------
(0 rows)

--
-- RAISE statement
--
-- a condition name closes the execution path by the related exception
create function sw_f9()
returns int as $$
begin
  raise division_by_zero;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f9');
 plpgsql_check_function 
------------------------
(0 rows)

-- the errcode option is preferred against the condition name
create function sw_f10()
returns int as $$
begin
  raise division_by_zero using errcode = 'unique_violation';
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f10');
 plpgsql_check_function 
------------------------
(0 rows)

-- an errcode that cannot be evaluated at the check time
create function sw_f11(c text)
returns int as $$
begin
  raise exception 'broken' using errcode = c;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f11');
 plpgsql_check_function 
------------------------
(0 rows)

-- the doubled percent is not a placeholder, so the message needs no
-- parameter for it
create function sw_f12()
returns int as $$
begin
  raise notice '100%% done, value %', 1;
  return 1;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f12');
 plpgsql_check_function 
------------------------
(0 rows)

--
-- exception handlers
--
-- the raised exception is matched by the name of its category
create function sw_f13()
returns int as $$
begin
  begin
    raise division_by_zero;
  exception when data_exception then
    return -1;
  end;
  return 0;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f13', extra_warnings => true);
            plpgsql_check_function             
-----------------------------------------------
 warning extra:00000:8:RETURN:unreachable code
(1 row)

-- an exception that cannot be raised by the protected statements is
-- reported as an unused handler
create function sw_f14()
returns int as $$
begin
  begin
    raise division_by_zero;
  exception when unique_violation then
    return -1;
  end;
  return 0;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f14', extra_warnings => true);
            plpgsql_check_function             
-----------------------------------------------
 warning extra:00000:8:RETURN:unreachable code
(1 row)

-- a block whose body is closed by an exception and whose handler raises
-- another exception is closed by the exception of the handler
create function sw_f14b()
returns int as $$
begin
  begin
    raise division_by_zero;
  exception when division_by_zero then
    raise unique_violation;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f14b', extra_warnings => true);
 plpgsql_check_function 
------------------------
(0 rows)

-- when the handlers only re-raise the caught exception, the block is
-- closed by the exceptions raised by the protected body
create function sw_f14c(n int)
returns int as $$
begin
  begin
    if n = 1 then
      raise division_by_zero;
    else
      raise unique_violation;
    end if;
  exception
    when division_by_zero then
      raise;
    when unique_violation then
      raise;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f14c', extra_warnings => true);
 plpgsql_check_function 
------------------------
(0 rows)

--
-- RETURN statement
--
-- a function returning refcursor should return a refcursor variable
create function sw_f14d()
returns refcursor as $$
declare c text := 'some_cursor';
begin
  return c;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f14d', compatibility_warnings => true);
                                plpgsql_check_function                                
--------------------------------------------------------------------------------------
 warning:42804:4:RETURN:target type is different type than source type
 Detail: cast "text" value to "refcursor" type
 Hint: The input expression type does not have an assignment cast to the target type.
 Context: at RETURN of variable "c" declared on line 2
 compatibility:00000:4:RETURN:obsolete setting of refcursor or cursor variable
 Detail: Internal name of cursor should not be specified by users.
(6 rows)

create function sw_f14e()
returns refcursor as $$
declare c refcursor;
begin
  open c for select 1;
  return c;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f14e', compatibility_warnings => true);
 plpgsql_check_function 
------------------------
(0 rows)

--
-- RETURN NEXT statement
--
-- the returned value is a single OUT variable
create function sw_f15(out a int)
returns setof int as $$
begin
  a := 1;
  return next;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f15');
 plpgsql_check_function 
------------------------
(0 rows)

-- more OUT variables are collected into a row
create function sw_f16(out a int, out b int)
returns setof record as $$
begin
  a := 1; b := 2;
  return next;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f16');
 plpgsql_check_function 
------------------------
(0 rows)

-- the types of the row fields have to match the result type
create table sw_tab1(a int, b int);
create function sw_f17(out a int, out b text)
returns setof record as $$
begin
  a := 1; b := 'x';
  return next;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f17');
 plpgsql_check_function 
------------------------
(0 rows)

-- a record variable is returned
create function sw_f18()
returns setof sw_tab1 as $$
declare r sw_tab1;
begin
  r := (1,2);
  return next r;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f18');
 plpgsql_check_function 
------------------------
(0 rows)

--
-- transaction control statements
--
-- COMMIT and ROLLBACK are allowed in a procedure only
create function sw_f19()
returns void as $$
begin
  commit;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f19');
                plpgsql_check_function                
------------------------------------------------------
 error:2D000:3:COMMIT:invalid transaction termination
(1 row)

create procedure sw_p1()
as $$
begin
  commit;
  rollback;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_p1');
 plpgsql_check_function 
------------------------
(0 rows)

-- inside a block with an exception handler a subtransaction is active,
-- so neither COMMIT nor ROLLBACK can be used
create procedure sw_p2()
as $$
begin
  begin
    commit;
  exception when others then
    null;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_p2');
                       plpgsql_check_function                        
---------------------------------------------------------------------
 error:2D000:4:COMMIT:cannot commit while a subtransaction is active
(1 row)

create procedure sw_p3()
as $$
begin
  begin
    rollback;
  exception when others then
    null;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_p3');
                          plpgsql_check_function                          
--------------------------------------------------------------------------
 error:2D000:4:ROLLBACK:cannot roll back while a subtransaction is active
(1 row)

--
-- dynamic SQL
--
-- OPEN FOR EXECUTE is checked like the other dynamic statements
create function sw_f20(p text)
returns int as $$
declare
  c refcursor;
  r int;
begin
  open c for execute p using 1;
  fetch c into r;
  close c;
  return r;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f20');
 plpgsql_check_function 
------------------------
(0 rows)

-- when the query text is not known, the result of the dynamic SQL cannot
-- be determined, and the record variable stays without a descriptor
create function sw_f21(p text)
returns int as $$
declare r record;
begin
  execute p into r;
  return r.x;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f21');
                                plpgsql_check_function                                
--------------------------------------------------------------------------------------
 warning:00000:4:EXECUTE:cannot determinate a result of dynamic SQL
 Detail: There is a risk of related false alarms.
 Hint: Don't use dynamic SQL and record type together, when you would check function.
 error:55000:5:RETURN:record "r" is not assigned yet
 Detail: The tuple structure of a not-yet-assigned record is indeterminate.
 Context: SQL expression "r.x"
(6 rows)

-- the expression of EXECUTE is built by a function which does not
-- sanitize its argument, and the position of the unsafe value is unknown
create function sw_f21b(p text)
returns void as $$
begin
  execute upper(p);
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f21b', security_warnings => true);
                           plpgsql_check_function                            
-----------------------------------------------------------------------------
 security:00000:3:EXECUTE:text type variable is not sanitized
 Query: upper(p)
 --           ^
 Detail: The EXECUTE expression is SQL injection vulnerable.
 Hint: Use quote_ident, quote_literal or format function to secure variable.
(5 rows)

-- a record variable with a declared type is known, so no warning is raised
create function sw_f22(p text)
returns int as $$
declare r sw_tab1;
begin
  execute p into r;
  return r.a;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_f22');
                                plpgsql_check_function                                
--------------------------------------------------------------------------------------
 warning:00000:4:EXECUTE:cannot determinate a result of dynamic SQL
 Detail: There is a risk of related false alarms.
 Hint: Don't use dynamic SQL and record type together, when you would check function.
(3 rows)

drop function sw_f1(int);
drop function sw_f2(int);
drop function sw_f3(int);
drop function sw_f4(int);
drop function sw_f4b(int);
drop function sw_f4c(int);
drop function sw_f5(int);
drop function sw_f6(int);
drop function sw_f7(int);
drop function sw_f8(int);
drop function sw_f9();
drop function sw_f10();
drop function sw_f11(text);
drop function sw_f12();
drop function sw_f13();
drop function sw_f14();
drop function sw_f14b();
drop function sw_f14c(int);
drop function sw_f14d();
drop function sw_f14e();
drop function sw_f15();
drop function sw_f16();
drop function sw_f17();
drop function sw_f18();
drop function sw_f19();
drop procedure sw_p1();
drop procedure sw_p2();
drop procedure sw_p3();
drop function sw_f20(text);
drop function sw_f21(text);
drop function sw_f21b(text);
drop function sw_f22(text);
drop table sw_tab1;
-- Bare RAISE needs an active handler, including through nested blocks
create function sw_bare_raise() returns int as $$
begin
  raise;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_bare_raise');
                                  plpgsql_check_function                                  
------------------------------------------------------------------------------------------
 error:0Z002:3:RAISE:RAISE without parameters cannot be used outside an exception handler
(1 row)

create function sw_handler_raise() returns void as $$
begin
  raise division_by_zero;
exception when division_by_zero then
  begin
    raise;
  end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('sw_handler_raise');
 plpgsql_check_function 
------------------------
(0 rows)

drop function sw_bare_raise();
drop function sw_handler_raise();