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, and no WHEN part matches, then the -- CASE_NOT_FOUND exception is raised, so the execution path is closed -- when 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 ------------------------ (0 rows) -- the CASE_NOT_FOUND exception can be handled create function sw_f5b(n int) returns int as $$ begin begin case n when 1 then return 10; end case; exception when case_not_found then return -1; end; end; $$ language plpgsql; select * from plpgsql_check_function('sw_f5b'); plpgsql_check_function ------------------------ (0 rows) -- 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:3:IF: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 IF (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 <> begin <> 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_f5b(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(); -- MOVE has no result target, even when the cursor query has several columns create function sw_move(distance int) returns void as $$ declare c cursor for select 1 as a, 2 as b; begin open c; move forward distance from c; close c; end; $$ language plpgsql; select * from plpgsql_check_function('sw_move(int)'); plpgsql_check_function ------------------------ (0 rows) drop function sw_move(int); -- Check before execution initializes the hidden CASE variable's type create type sw_single_field as (a int); create function sw_case_single(v sw_single_field) returns int as $$ begin case v when row(1)::sw_single_field then return 1; else return 2; end case; end; $$ language plpgsql; select * from plpgsql_check_function('sw_case_single(sw_single_field)'); plpgsql_check_function ------------------------ (0 rows) select sw_case_single(row(1)::sw_single_field) = 1 and sw_case_single(row(2)::sw_single_field) = 2 as valid; valid ------- t (1 row) drop function sw_case_single(sw_single_field); drop type sw_single_field; -- Earlier exception paths must remain visible when later paths are merged create function sw_exception_union(p boolean) returns int as $$ begin begin if p then raise division_by_zero; else raise unique_violation; end if; exception when division_by_zero then null; when unique_violation then return 2; end; return 1; end; $$ language plpgsql; select * from plpgsql_check_function('sw_exception_union(boolean)'); plpgsql_check_function ------------------------ (0 rows) drop function sw_exception_union(boolean); -- A first-handler rethrow keeps the caught SQLSTATE for outer handlers create function sw_nested_rethrow() returns int as $$ begin begin begin raise division_by_zero; exception when division_by_zero then raise; end; exception when division_by_zero then null; end; return 1; end; $$ language plpgsql; select * from plpgsql_check_function('sw_nested_rethrow()'); plpgsql_check_function ------------------------ (0 rows) drop function sw_nested_rethrow(); -- Dead exits must not change which exception handler is reachable create function sw_dead_flow() returns int as $$ begin begin raise division_by_zero; raise unique_violation; exception when division_by_zero then return 1; when unique_violation then null; end; end; $$ language plpgsql; select sqlstate, message from plpgsql_check_function_tb('sw_dead_flow()'); sqlstate | message ----------+------------------ 00000 | unreachable code (1 row) -- Expressions in dead statements still need to be checked create function sw_dead_expression() returns int as $$ begin return 1; perform sw_missing_dead_column; end; $$ language plpgsql; select exists ( select from plpgsql_check_function_tb('sw_dead_expression()') where sqlstate = '42703') as checked; checked --------- t (1 row) drop function sw_dead_flow(); drop function sw_dead_expression(); -- Pragmas in one branch or handler must not affect its siblings create function sw_pragma_siblings(p boolean) returns void as $$ begin if p then perform plpgsql_check_pragma('disable:check'); else perform sw_missing_else_column; end if; case when p then perform plpgsql_check_pragma('disable:check'); else perform sw_missing_case_column; end case; begin raise division_by_zero; exception when unique_violation then perform plpgsql_check_pragma('disable:check'); when division_by_zero then perform sw_missing_handler_column; end; end; $$ language plpgsql; select count(*) = 3 as sibling_checks from plpgsql_check_function_tb('sw_pragma_siblings(boolean)', fatal_errors => false) where sqlstate = '42703'; sibling_checks ---------------- t (1 row) drop function sw_pragma_siblings(boolean); -- Check dynamic cursors before execution can prime record-field metadata create function sw_dynamic_cursor() returns int as $$ declare c refcursor; r record; begin open c for execute 'select 1 as a'; fetch c into r; close c; return r.a; end; $$ language plpgsql; create function sw_parameter_cursor(v int) returns int as $$ declare c refcursor; r record; begin open c for execute 'select $1::int as a' using v; fetch c into r; close c; return r.a; end; $$ language plpgsql; -- A later static OPEN supersedes the earlier dynamic query create function sw_cursor_reopen(v int) returns int as $$ declare c refcursor; r record; begin open c for execute 'select $1::int as a' using v; fetch c into r; close c; open c for select v as b; fetch c into r; close c; return r.b; end; $$ language plpgsql; -- FETCH into scalar targets also invalidates their previous traced constants create function sw_dynamic_scalar() returns void as $$ declare c refcursor; s text := 'select sw_missing_dynamic_column'; begin open c for execute $q$select 'select 1'::text$q$; fetch c into s; close c; execute s; end; $$ language plpgsql; select * from plpgsql_check_function('sw_dynamic_cursor()'); plpgsql_check_function ------------------------ (0 rows) select * from plpgsql_check_function('sw_parameter_cursor(int)'); plpgsql_check_function ------------------------ (0 rows) select * from plpgsql_check_function('sw_cursor_reopen(int)'); plpgsql_check_function ------------------------ (0 rows) select * from plpgsql_check_function('sw_dynamic_scalar()'); plpgsql_check_function ------------------------ (0 rows) select sw_dynamic_cursor() = 1 and sw_parameter_cursor(3) = 3 and sw_cursor_reopen(4) = 4 as valid; valid ------- t (1 row) do $$ begin perform sw_dynamic_scalar(); end; $$; drop function sw_dynamic_cursor(); drop function sw_parameter_cursor(int); drop function sw_cursor_reopen(int); drop function sw_dynamic_scalar(); -- FETCH from a cursor that is not opened in the function (so its query -- is unknown) has to be checked without crash, also when there is no -- OPEN FOR EXECUTE in the function. The passive mode does the same check. create function sw_fetch_unbound(c refcursor) returns int as $$ declare v int; begin fetch c into v; return v; end; $$ language plpgsql; select * from plpgsql_check_function('sw_fetch_unbound(refcursor)'); plpgsql_check_function ------------------------ (0 rows) set plpgsql_check.mode = 'every_start'; begin; declare sw_fetch_cur cursor for select 1; select sw_fetch_unbound('sw_fetch_cur'); sw_fetch_unbound ------------------ 1 (1 row) commit; reset plpgsql_check.mode; drop function sw_fetch_unbound(refcursor); create function sw_exit_loop(p boolean) returns int as $$ begin loop if p then exit; end if; return 1; end loop; return 2; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_exit_loop(boolean)'); plpgsql_check_function ------------------------ (0 rows) drop function sw_exit_loop(boolean); create function sw_exit_loop(p boolean) returns int as $$ begin loop exit when p; return 1; end loop; return 2; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_exit_loop'); plpgsql_check_function ------------------------ (0 rows) drop function sw_exit_loop(boolean); create function sw_exit_nested(p boolean) returns int as $$ begin <> loop loop exit outer_loop when p; return 1; end loop; end loop; return 2; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_exit_nested'); plpgsql_check_function ------------------------ (0 rows) drop function sw_exit_nested(boolean); create function sw_exit_block(p boolean) returns int as $$ begin <> begin exit target when p; return 1; end; return 2; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_exit_block'); plpgsql_check_function ------------------------ (0 rows) drop function sw_exit_block(boolean); create function sw_exit_missing(p boolean) returns int as $$ begin loop exit when p; return 1; end loop; return 1; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_exit_missing'); plpgsql_check_function ------------------------ (0 rows) drop function sw_exit_missing(boolean); create function sw_exit_inner() returns int as $$ begin loop loop exit; end loop; return 1; end loop; return 2; end; $$ language plpgsql; -- should be warning select * from plpgsql_check_function('sw_exit_inner'); plpgsql_check_function ----------------------------------------------- warning extra:00000:9:RETURN:unreachable code (1 row) drop function sw_exit_inner(); create function sw_continue_path() returns int as $$ declare i int := 0; begin loop i := i + 1; continue when i = 1; return i; end loop; return 3; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_continue_path'); plpgsql_check_function ----------------------------------------------- warning extra:00000:9:RETURN:unreachable code (1 row) drop function sw_continue_path(); create function sw_continue_outer() returns int as $$ begin <> for i in 1..2 loop loop continue outer_loop; end loop; end loop; return 1; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_continue_outer'); plpgsql_check_function ------------------------ (0 rows) drop function sw_continue_outer(); create function sw_dead_exit() returns int as $$ begin loop return 1; exit; end loop; return 2; end; $$ language plpgsql; -- should be warning select * from plpgsql_check_function('sw_dead_exit'); plpgsql_check_function ----------------------------------------------- warning extra:00000:5:EXIT:unreachable code warning extra:00000:7:RETURN:unreachable code (2 rows) drop function sw_dead_exit(); create function sw_handler_exit() returns int as $$ begin loop begin raise division_by_zero; exception when unique_violation then exit; when division_by_zero then return 1; end; end loop; return 2; end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_handler_exit'); plpgsql_check_function ------------------------ (0 rows) drop function sw_handler_exit(); create or replace function repro05_dynsql_query() returns void as $$ begin execute 'select 1' || undefined_var; end; $$ language plpgsql; create or replace function repro05_dynsql_param() returns void as $$ begin execute 'select $1' using undefined_var; end; $$ language plpgsql; create or replace function repro05_raise_errcode() returns void as $$ begin raise exception 'x' using errcode = undefined_var; end; $$ language plpgsql; select * from plpgsql_check_function('repro05_dynsql_query()', fatal_errors => false); plpgsql_check_function ------------------------------------------------------------- error:42703:3:EXECUTE:column "undefined_var" does not exist Query: 'select 1' || undefined_var -- ^ (3 rows) select * from plpgsql_check_function('repro05_dynsql_param()', fatal_errors => false); plpgsql_check_function ------------------------------------------------------------- error:42703:3:EXECUTE:column "undefined_var" does not exist Query: undefined_var -- ^ (3 rows) select * from plpgsql_check_function('repro05_raise_errcode()', fatal_errors => false); plpgsql_check_function ----------------------------------------------------------- error:42703:3:RAISE:column "undefined_var" does not exist Query: undefined_var -- ^ (3 rows) drop function repro05_dynsql_query(); drop function repro05_dynsql_param(); drop function repro05_raise_errcode(); -- expressions with FROM clause are not simple expressions, should not crash create table sw_from_tab(q text); create function sw_from_expr() returns void as $$ declare x text; r record; begin execute q from sw_from_tab; x := q from sw_from_tab; execute 'select ' || x; for r in execute q from sw_from_tab loop end loop; end; $$ language plpgsql; select * from plpgsql_check_function('sw_from_expr', security_warnings => true); plpgsql_check_function --------------------------------------------------------------------------------------- warning extra:42601:6:EXECUTE:expression is not pure expression Query: q from sw_from_tab -- ^ Detail: there is a possibility of unwanted behave Hint: Maybe you forgot to use a semicolon. security:00000:6:EXECUTE:the expression is not SQL injection safe Query: q from sw_from_tab Detail: Cannot ensure so dynamic EXECUTE statement is SQL injection secure. Hint: Use quote_ident, quote_literal or format function to secure variable. warning extra:42601:7:assignment:expression is not pure expression Query: x := q from sw_from_tab -- ^ Detail: there is a possibility of unwanted behave Hint: Maybe you forgot to use a semicolon. Context: at assignment to variable "x" declared on line 3 security:00000:8:EXECUTE:text type variable is not sanitized Query: 'select ' || x -- ^ Detail: The EXECUTE expression is SQL injection vulnerable. Hint: Use quote_ident, quote_literal or format function to secure variable. warning extra:42601:9:FOR over EXECUTE statement:expression is not pure expression Query: q from sw_from_tab -- ^ Detail: there is a possibility of unwanted behave Hint: Maybe you forgot to use a semicolon. security:00000:9:FOR over EXECUTE statement:the expression is not SQL injection safe Query: q from sw_from_tab Detail: Cannot ensure so dynamic EXECUTE statement is SQL injection secure. Hint: Use quote_ident, quote_literal or format function to secure variable. warning:00000:9:FOR over EXECUTE statement: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. warning extra:00000:4:DECLARE:never read variable "r" (33 rows) drop function sw_from_expr(); drop table sw_from_tab; -- an error in an expression should not cause other bogus errors -- when fatal_errors is false create procedure sw_nf_proc(inout a int) as $$ begin end; $$ language plpgsql; create function sw_nonfatal(p int) returns setof int as $$ declare c refcursor; c2 cursor(a int) for select a; v int; begin raise notice 'x' using errcode = nonexisting_errcode; raise notice 'x' using errcode = (1/0)::text; execute nonexisting_query into v; execute 'select $1' using nonexisting_param; execute (1/0)::text; open c for execute nonexisting_query; return query execute nonexisting_query; call sw_nf_proc(nonexisting_arg); open c for select 1/0; for r in c2(nonexisting_arg) loop raise notice '%', r.a + nonexisting_var; end loop; end; $$ language plpgsql; select * from plpgsql_check_function('sw_nonfatal', fatal_errors => false); plpgsql_check_function ------------------------------------------------------------------------ error:42703:7:RAISE:column "nonexisting_errcode" does not exist Query: nonexisting_errcode -- ^ error:22012:8:RAISE:division by zero error:42703:9:EXECUTE:column "nonexisting_query" does not exist Query: nonexisting_query -- ^ error:42703:10:EXECUTE:column "nonexisting_param" does not exist Query: nonexisting_param -- ^ error:22012:11:EXECUTE:division by zero error:42703:12:OPEN:column "nonexisting_query" does not exist Query: nonexisting_query -- ^ error:42703:13:RETURN QUERY:column "nonexisting_query" does not exist Query: nonexisting_query -- ^ error:42703:14:CALL:column "nonexisting_arg" does not exist Query: call sw_nf_proc(nonexisting_arg) -- ^ error:22012:15:OPEN:division by zero error:42703:16:FOR over cursor:column "nonexisting_arg" does not exist Query: nonexisting_arg -- ^ error:42703:18:RAISE:column "nonexisting_var" does not exist Query: r.a + nonexisting_var -- ^ warning extra:00000:3:DECLARE:never read variable "c" warning extra:00000:5:DECLARE:never read variable "v" warning extra:00000:unused parameter "p" (30 rows) drop function sw_nonfatal(int); drop procedure sw_nf_proc(int); -- WHEN OTHERS catches the exception, the code after the block is reachable create function sw_others1(p int) returns int as $$ begin begin raise exception 'error %', p; exception when others then raise notice 'caught'; end; return 1; end; $$ language plpgsql; select * from plpgsql_check_function('sw_others1'); plpgsql_check_function ------------------------ (0 rows) -- the end of the function is reachable, the missing RETURN is reported create function sw_others2(p int) returns int as $$ begin begin raise exception 'error %', p; exception when others then raise notice 'caught'; end; end; $$ language plpgsql; select * from plpgsql_check_function('sw_others2'); plpgsql_check_function ------------------------------------------------------------ error:2F005:control reached end of function without RETURN (1 row) drop function sw_others1(int); drop function sw_others2(int); -- returned value or query without columns create function sw_rq0() returns table(a int) as $$ begin return query select; end; $$ language plpgsql; -- should fail, the structure of query doesn't match select * from plpgsql_check_function('sw_rq0'); plpgsql_check_function ----------------------------------------------------------------------------------- error:42804:3:RETURN QUERY:structure of query does not match function result type Detail: Number of returned columns (0) does not match expected column count (1). (2 rows) create type sw_rr_type as (a int, b int); create function sw_rr0() returns sw_rr_type as $$ begin return row(); end; $$ language plpgsql; -- should fail, the structure of returned record doesn't match select * from plpgsql_check_function('sw_rr0'); plpgsql_check_function ---------------------------------------------------------------------------------- error:42804:3:RETURN:returned record type does not match expected record type Detail: Number of returned columns (0) does not match expected column count (2). (2 rows) create function sw_rr1() returns record as $$ begin return row(); end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_rr1'); plpgsql_check_function ------------------------ (0 rows) create function sw_rr2() returns text as $$ begin return row(); end; $$ language plpgsql; -- is ok select * from plpgsql_check_function('sw_rr2'); plpgsql_check_function ------------------------ (0 rows) drop function sw_rq0(); drop function sw_rr0(); drop function sw_rr1(); drop function sw_rr2(); drop type sw_rr_type; -- the plan checks should be raised only once create table sw_plan_tab(a int primary key); create function sw_plan_checks(p int) returns int as $$ declare x int; begin x := (select a from sw_plan_tab where a = p for update); raise exception using errcode = (select 'P0001' from sw_plan_tab where a = p for update); return x; end; $$ language plpgsql stable; select * from plpgsql_check_function('sw_plan_checks', fatal_errors => false); plpgsql_check_function -------------------------------------------------------------------------------------- error:0A000:4:assignment:SELECT FOR UPDATE is not allowed in a non volatile function Query: x := (select a from sw_plan_tab where a = p for update) -- ^ Context: at assignment to variable "x" declared on line 2 error:0A000:5:RAISE:SELECT FOR UPDATE is not allowed in a non volatile function Query: (select 'P0001' from sw_plan_tab where a = p for update) -- ^ warning extra:00000:6:RETURN:unreachable code (8 rows) drop function sw_plan_checks(int); drop table sw_plan_tab; -- the structure of result of function returning setof record is not -- known, and the scalar value is casted to the column of composite type -- with one column create type sw_onecol as (a int); create function sw_rn_record() returns setof record as $$ declare r record; begin for r in select 1 as a, 2 as b loop return next r; end loop; return; end; $$ language plpgsql; create function sw_rn_record_scalar() returns setof record as $$ declare v int := 1; begin return next v; return; end; $$ language plpgsql; create function sw_rn_onecol() returns setof sw_onecol as $$ declare v int := 1; d date := current_date; begin return next v; -- should be reported return next d; return; end; $$ language plpgsql; select * from plpgsql_check_function('sw_rn_record()'); plpgsql_check_function ------------------------ (0 rows) select * from plpgsql_check_function('sw_rn_record_scalar()'); plpgsql_check_function ------------------------ (0 rows) select * from plpgsql_check_function('sw_rn_onecol()'); plpgsql_check_function ---------------------------------------------------------------------------------- warning:42804:8:RETURN NEXT:target type is different type than source type Detail: cast "date" value to "integer" type Hint: There are no possible explicit coercion between those types, possibly bug! Context: at RETURN NEXT of variable "d" declared on line 4 (4 rows) drop function sw_rn_record(); drop function sw_rn_record_scalar(); drop function sw_rn_onecol(); drop type sw_onecol; -- -- RETURN of trigger variable (PROMISE datum) should be checked -- like RETURN of any other scalar variable -- create table sw_trg_t(a int); create function sw_trg_ret_promise() returns trigger as $$ begin return tg_op; end; $$ language plpgsql; select * from plpgsql_check_function('sw_trg_ret_promise()', 'sw_trg_t'); plpgsql_check_function ----------------------------------------------------------------------------------------------- error:42804:3:RETURN:cannot return non-composite value from function returning composite type (1 row) drop function sw_trg_ret_promise(); drop table sw_trg_t; -- -- the FOUND variable is declared only once, in the namespace of the -- function -- -- the shadowing of the parameter is reported only once create function sw_found_param(found int) returns int as $$ begin begin return 1; end; end; $$ language plpgsql; select * from plpgsql_check_function('sw_found_param(int)'); plpgsql_check_function --------------------------------------------------------------- warning:00000:2:statement block:parameter "found" is shadowed Detail: Local auto variable shadows function parameter. warning extra:00000:unused parameter "found" (3 rows) -- the local variable shadows the FOUND variable create function sw_found_var() returns int as $$ declare found boolean := false; begin begin perform 1; end; if not found then return 0; end if; return 1; end; $$ language plpgsql; select * from plpgsql_check_function('sw_found_var()'); plpgsql_check_function ---------------------------------------------------------------------------------------------- warning extra:00000:3:statement block:variable "found" shadows a previously defined variable Hint: SET plpgsql.extra_warnings TO 'shadowed_variables' (2 rows) drop function sw_found_param(int); drop function sw_found_var(); -- the label used by EXIT and CONTINUE is not checked again create function sw_label_keyword() returns void as $$ begin <> loop exit table when random() > 0.5; continue table; end loop; end; $$ language plpgsql; select * from plpgsql_check_function('sw_label_keyword()'); plpgsql_check_function ---------------------------------------------------------------- warning:00000:4:LOOP:name of label "table" is reserved keyword Detail: The reserved keyword was used as label name. (2 rows) drop function sw_label_keyword(); -- the diagnostics raised after the check of nested statements are -- related to the right statement create function sw_err_stmt(a int) returns int as $$ begin return 1; if a > 0 then a := a + 1; a := a + 2; end if; case when a > 0 then a := a + 1; when a + nonexisting_col > 0 then a := 0; else null; end case; return a; end; $$ language plpgsql; select * from plpgsql_check_function('sw_err_stmt(int)', fatal_errors => false); plpgsql_check_function ------------------------------------------------------------ warning extra:00000:4:IF:unreachable code error:42703:8:CASE:column "nonexisting_col" does not exist Query: a + nonexisting_col > 0 -- ^ (4 rows) drop function sw_err_stmt(int); -- the rest of statement should be checked after an error -- in CASE selector or in constant dynamic query create function sw_nonfatal2(a int) returns int as $$ declare r record; v int; begin case a + nonexisting_col when 1 then v := 1 + nonexisting_var; else v := 2; end case; for r in execute 'select nonexisting_col2 as x' loop v := v + nonexisting_var2; end loop; return v; end; $$ language plpgsql; select * from plpgsql_check_function('sw_nonfatal2', fatal_errors => false); plpgsql_check_function ------------------------------------------------------------------------------------ error:42703:6:CASE:column "nonexisting_col" does not exist Query: a + nonexisting_col -- ^ error:42703:7:assignment:column "nonexisting_var" does not exist Query: v := 1 + nonexisting_var -- ^ Context: at assignment to variable "v" declared on line 4 error:42703:10:FOR over EXECUTE statement:column "nonexisting_col2" does not exist Query: select nonexisting_col2 as x -- ^ error:42703:12:assignment:column "nonexisting_var2" does not exist Query: v := v + nonexisting_var2 -- ^ Context: at assignment to variable "v" declared on line 4 warning extra:00000:3:DECLARE:never read variable "r" (15 rows) select * from plpgsql_check_function('sw_nonfatal2'); plpgsql_check_function ------------------------------------------------------------ error:42703:6:CASE:column "nonexisting_col" does not exist Query: a + nonexisting_col -- ^ (3 rows) create function sw_case_text(a int) returns int as $$ begin case a::text when 'abc' then return 1; else return 2; end case; end; $$ language plpgsql; select * from plpgsql_check_function('sw_case_text', fatal_errors => false); plpgsql_check_function ------------------------ (0 rows) drop function sw_nonfatal2(int); drop function sw_case_text(int); -- the query of OPEN FOR is not stored in the cursor variable, so the next -- OPEN doesn't check it again, the variable is not considered as a bound -- cursor, and FETCH uses the query of the last OPEN create table sw_open_tab(a int); create function sw_open_reopen(v numeric) returns void as $$ declare c refcursor; r record; begin open c for select a from sw_open_tab where a = v; close c; open c for select 1 as x; fetch c into r; raise notice '%', r.x; close c; end; $$ language plpgsql; select * from plpgsql_check_function('sw_open_reopen(numeric)', performance_warnings => true); plpgsql_check_function -------------------------------------------------------------------------------------------------------------------------- performance:42804:6:OPEN:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause Query: select a from sw_open_tab where a = v -- ^ Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute Hint: Check a variable type - int versus numeric (5 rows) create function sw_open_assign() returns refcursor as $$ declare c refcursor; d refcursor; begin open c for select 1; close c; open d for select 2; c := d; return c; end; $$ language plpgsql; select * from plpgsql_check_function('sw_open_assign()', compatibility_warnings => true); plpgsql_check_function ------------------------ (0 rows) create function sw_open_dynamic() returns int as $$ declare c refcursor; r record; begin open c for select 1 as a, 2 as b; close c; open c for execute 'select 3 as x'; fetch c into r; close c; return r.x; end; $$ language plpgsql; select * from plpgsql_check_function('sw_open_dynamic()'); plpgsql_check_function ------------------------ (0 rows) select sw_open_dynamic(); sw_open_dynamic ----------------- 3 (1 row) drop function sw_open_reopen(numeric); drop function sw_open_assign(); drop function sw_open_dynamic(); drop table sw_open_tab; -- FETCH doesn't check the query of the cursor again (the query was checked -- by OPEN), it checks only the assignment of the result of the query create table sw_fetch_tab(a int); create function sw_fetch_static(v numeric) returns int as $$ declare c cursor for select a from sw_fetch_tab where a = v; d refcursor; r record; s int; begin open c; fetch c into r; open d for select a from sw_fetch_tab where a = v; fetch d into s; return r.a + s; end; $$ language plpgsql stable; select * from plpgsql_check_function('sw_fetch_static(numeric)', performance_warnings => true); plpgsql_check_function -------------------------------------------------------------------------------------------------------------------------- performance:42804:8:OPEN:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause Query: select a from sw_fetch_tab where a = v -- ^ Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute Hint: Check a variable type - int versus numeric performance:42804:10:OPEN:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause Query: select a from sw_fetch_tab where a = v -- ^ Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute Hint: Check a variable type - int versus numeric (10 rows) create function sw_fetch_dynamic(tname text) returns int as $$ declare c refcursor; r record; q text; begin open c for execute 'select * from ' || tname; fetch c into r; close c; q := 'select 1 as a'; open c for execute q; q := 'select 2 as b'; fetch c into r; close c; return r.a; end; $$ language plpgsql; select * from plpgsql_check_function('sw_fetch_dynamic(text)', security_warnings => true); plpgsql_check_function -------------------------------------------------------------------------------------- security:00000:7:OPEN:text type variable is not sanitized Query: 'select * from ' || tname -- ^ Detail: The EXECUTE expression is SQL injection vulnerable. Hint: Use quote_ident, quote_literal or format function to secure variable. warning:00000:8:FETCH: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. (8 rows) select sw_fetch_dynamic('sw_fetch_tab'); sw_fetch_dynamic ------------------ 1 (1 row) create function sw_fetch_nonfatal() returns void as $$ declare c refcursor; r record; begin open c for execute 'select nocol from sw_fetch_tab'; fetch c into r; close c; open c for select nocol from sw_fetch_tab; fetch c into r; close c; end; $$ language plpgsql; select * from plpgsql_check_function('sw_fetch_nonfatal()', fatal_errors => false, extra_warnings => false); plpgsql_check_function -------------------------------------------------- error:42703:6:OPEN:column "nocol" does not exist Query: select nocol from sw_fetch_tab -- ^ error:42703:9:OPEN:column "nocol" does not exist Query: select nocol from sw_fetch_tab -- ^ (6 rows) drop function sw_fetch_static(numeric); drop function sw_fetch_dynamic(text); drop function sw_fetch_nonfatal(); drop table sw_fetch_tab;