load 'plpgsql'; create extension if not exists plpgsql_check; set client_min_messages to notice; set plpgsql_check.regress_test_mode = true; -- -- check function statement tests -- --should fail - is not plpgsql select * from plpgsql_check_function_tb('session_user()'); ERROR: "session_user"() is not a plpgsql function create table t1(a int, b int); create table pa (id int, pa_id character varying(32), status character varying(60)); create table ml(ml_id character varying(32), status_from character varying(60), pa_id character varying(32), xyz int); create function f1() returns void as $$ begin if false then update t1 set c = 30; end if; if false then raise notice '%', r.c; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | f1 | 7 | RAISE | 42P01 | missing FROM-clause entry for table "r" | | | error | 1 | r.c | (2 rows) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | (1 row) drop function f1(); create function f1() returns void as $$ begin if false then insert into t1 values(10,20); update t1 set a = 10; delete from t1; end if; end; $$ language plpgsql stable; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------------+--------+------+-------+----------+------------------------------+--------- f1 | 4 | SQL statement | 0A000 | INSERT is not allowed in a non volatile function | | | error | 1 | insert into t1 values(10,20) | f1 | 5 | SQL statement | 0A000 | UPDATE is not allowed in a non volatile function | | | error | 1 | update t1 set a = 10 | f1 | 6 | SQL statement | 0A000 | DELETE is not allowed in a non volatile function | | | error | 1 | delete from t1 | (3 rows) drop function f1(); create function f1() returns void as $$ declare r record; begin if false then for r in update t1 set a = a + 1 returning * loop raise notice '%', r.a; end loop; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function f1(); create function f1() returns void as $$ declare r record; begin if false then for r in update t1 set a = a + 1 returning * loop raise notice '%', r.a; end loop; end if; end; $$ language plpgsql stable; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+----------------------+----------+--------------------------------------------------+--------+------+-------+----------+-------------------------------------+--------- f1 | 5 | FOR over SELECT rows | 0A000 | UPDATE is not allowed in a non volatile function | | | error | 1 | update t1 set a = a + 1 returning * | (1 row) drop function f1(); create function g1(out a int, out b int) as $$ select 10,20; $$ language sql; create function f1() returns void as $$ declare r record; begin r := g1(); if false then raise notice '%', r.c; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-----------------------------+--------+------+-------+----------+-------+--------------------------- f1 | 6 | RAISE | 42703 | record "r" has no field "c" | | | error | | | PL/pgSQL expression "r.c" (1 row) select f1(); f1 ---- (1 row) drop function f1(); drop function g1(); create function g1(out a int, out b int) returns setof record as $$ select * from t1; $$ language sql; create function f1() returns void as $$ declare r record; begin for r in select * from g1() loop raise notice '%', r.c; end loop; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-----------------------------+--------+------+-------+----------+-------+--------------------------- f1 | 6 | RAISE | 42703 | record "r" has no field "c" | | | error | | | PL/pgSQL expression "r.c" (1 row) select f1(); f1 ---- (1 row) create or replace function f1() returns void as $$ declare r record; begin for r in select * from g1() loop r.c := 20; end loop; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+-----------------------------+--------+------+-------+----------+-------+--------------------------------------------------------------- f1 | 6 | assignment | 42703 | record "r" has no field "c" | | | error | | | at assignment to field "c" of variable "r" declared on line 2 (1 row) select f1(); f1 ---- (1 row) drop function f1(); drop function g1(); create function f1() returns int as $$ declare r int; begin if false then r := a + b; end if; return r; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+---------------------------+--------+------+-------+----------+------------+-------------------------------------------------- f1 | 5 | assignment | 42703 | column "a" does not exist | | | error | 6 | r := a + b | at assignment to variable "r" declared on line 2 (1 row) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1() returns void as $$ declare r int[]; begin if false then r[c+10] := 20; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+---------------------------+--------+------+-------+----------+---------------+-------------------------------------------------- f1 | 5 | assignment | 42703 | column "c" does not exist | | | error | 3 | r[c+10] := 20 | at assignment to variable "r" declared on line 2 (1 row) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1() returns void as $$ declare r int; begin if false then r[10] := 20; end if; end; $$ language plpgsql set search_path = public; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+------------------------------------------------------------------------+--------+------+-------+----------+-------------+-------------------------------------------------- f1 | 5 | assignment | 42804 | cannot subscript type integer because it does not support subscripting | | | error | 1 | r[10] := 20 | at assignment to variable "r" declared on line 2 (1 row) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1_trg() returns trigger as $$ begin if new.a > 10 then raise notice '%', new.b; raise notice '%', new.c; end if; return new; end; $$ language plpgsql; create trigger t1_f1 before insert on t1 for each row execute procedure f1_trg(); insert into t1 values(6,30); select * from plpgsql_check_function_tb('f1_trg()','t1'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------+--------+------+-------+----------+-------+----------------------------- f1_trg | 5 | RAISE | 42703 | record "new" has no field "c" | | | error | | | PL/pgSQL expression "new.c" (1 row) insert into t1 values(6,30); create or replace function f1_trg() returns trigger as $$ begin new.a := new.a + 10; new.b := new.b + 10; new.c := 30; return new; end; $$ language plpgsql; -- should to fail select * from plpgsql_check_function_tb('f1_trg()','t1'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+-------------------------------+--------+------+-------+----------+-------+----------------------------------------------------------------- f1_trg | 5 | assignment | 42703 | record "new" has no field "c" | | | error | | | at assignment to field "c" of variable "new" declared on line 0 (1 row) -- should to fail but not crash insert into t1 values(6,30); ERROR: record "new" has no field "c" CONTEXT: PL/pgSQL assignment "new.c := 30" PL/pgSQL function f1_trg() line 5 at assignment create or replace function f1_trg() returns trigger as $$ begin new.a := new.a + 10; new.b := new.b + 10; return new; end; $$ language plpgsql; -- ok select * from plpgsql_check_function_tb('f1_trg()', 't1'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) -- ok insert into t1 values(6,30); create or replace function f1_trg() returns trigger as $$ begin new.a := new.a + 10; new.b := new.b + 10; return null; end; $$ language plpgsql; -- ok select * from plpgsql_check_function_tb('f1_trg()', 't1'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) insert into t1 values(60,300); select * from t1; a | b ----+---- 6 | 30 6 | 30 16 | 40 (3 rows) insert into t1 values(600,30); select * from t1; a | b ----+---- 6 | 30 6 | 30 16 | 40 (3 rows) drop trigger t1_f1 on t1; drop function f1_trg(); -- test of showing caret on correct place for multiline queries create or replace function f1() returns void as $$ begin select var from foo; end; $$ language plpgsql; select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+-------------------------------+--------+------+-------+----------+--------+--------- f1 | 3 | SQL statement | 42P01 | relation "foo" does not exist | | | error | 23 | select+| | | | | | | | | | var +| | | | | | | | | | from+| | | | | | | | | | foo | (1 row) drop function f1(); create or replace function f1() returns int as $$ begin return (select a from t1 where hh = 20); end; $$ language plpgsql; select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+----------------------------+--------+------+-------+----------+----------------------------+--------- f1 | 3 | RETURN | 42703 | column "hh" does not exist | | | error | 50 | (select a +| | | | | | | | | | from t1 +| | | | | | | | | | where hh = 20) | (1 row) create or replace function f1() returns int as $$ begin return (select a from txxxxxxx where hh = 20); end; $$ language plpgsql; select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+------------------------------------+--------+------+-------+----------+----------------------------+--------- f1 | 3 | RETURN | 42P01 | relation "txxxxxxx" does not exist | | | error | 29 | (select a +| | | | | | | | | | from txxxxxxx+| | | | | | | | | | where hh = 20) | (1 row) drop function f1(); drop table t1; -- raise warnings when target row has different number of attributies in -- SELECT INTO statement create or replace function f1() returns void as $$ declare a1 int; a2 int; begin select 10,20 into a1,a2; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+--------------------------+--------+------+---------------+----------+-------+--------- f1 | 2 | DECLARE | 00000 | never read variable "a1" | | | warning extra | | | f1 | 2 | DECLARE | 00000 | never read variable "a2" | | | warning extra | | | (2 rows) create or replace function f1() returns void as $$ declare a1 int; begin select 10,20 into a1; end; $$ language plpgsql; -- raise warning select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+------------------------------------------+---------------------------------------------------------------+-------------------------------------------------+---------------+----------+--------------+---------------------------------- f1 | 4 | SQL statement | 00000 | too many attributes for target variables | There are less target variables than output columns in query. | Check target variables in SELECT INTO statement | warning | 1 | select 10,20 | at SQL statement to a1 variables f1 | 2 | DECLARE | 00000 | never read variable "a1" | | | warning extra | | | (2 rows) create or replace function f1() returns void as $$ declare a1 int; a2 int; begin select 10 into a1,a2; end; $$ language plpgsql; -- raise warning select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+-----------------------------------------+---------------------------------------------------------------+--------------------------------------------------+---------------+----------+-----------+-------------------------------------- f1 | 4 | SQL statement | 00000 | too few attributes for target variables | There are more target variables than output columns in query. | Check target variables in SELECT INTO statement. | warning | 1 | select 10 | at SQL statement to a1, a2 variables f1 | 2 | DECLARE | 00000 | never read variable "a1" | | | warning extra | | | f1 | 2 | DECLARE | 00000 | never read variable "a2" | | | warning extra | | | (3 rows) -- bogus code set check_function_bodies to off; create or replace function f1() returns void as $$ adasdfsadf $$ language plpgsql; select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+--------------------------------------+--------+------+-------+----------+------------+--------------------------------------------------- f1 | | | 42601 | syntax error at or near "adasdfsadf" | | | error | 2 | +| compilation of PL/pgSQL function "f1" near line 1 | | | | | | | | | adasdfsadf+| | | | | | | | | | | (1 row) drop function f1(); create table t1(a int, b int); create function g1(out a int, out b int) as $$ select 10,20; $$ language sql; create function f1() returns void as $$ declare r record; begin r := g1(); if false then raise notice '%', r.c; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------- error:42703:6:RAISE:record "r" has no field "c" Context: PL/pgSQL expression "r.c" (2 rows) select f1(); f1 ---- (1 row) drop function f1(); drop function g1(); create function g1(out a int, out b int) returns setof record as $$ select * from t1; $$ language sql; create function f1() returns void as $$ declare r record; begin for r in select * from g1() loop raise notice '%', r.c; end loop; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------- error:42703:6:RAISE:record "r" has no field "c" Context: PL/pgSQL expression "r.c" (2 rows) select f1(); f1 ---- (1 row) create or replace function f1() returns void as $$ declare r record; begin for r in select * from g1() loop r.c := 20; end loop; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------------------------------ error:42703:6:assignment:record "r" has no field "c" Context: at assignment to field "c" of variable "r" declared on line 2 (2 rows) select f1(); f1 ---- (1 row) drop function f1(); drop function g1(); create function f1() returns int as $$ declare r int; begin if false then r := a + b; end if; return r; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ----------------------------------------------------------- error:42703:5:assignment:column "a" does not exist Query: r := a + b -- ^ Context: at assignment to variable "r" declared on line 2 (4 rows) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1() returns void as $$ declare r int[]; begin if false then r[c+10] := 20; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ----------------------------------------------------------- error:42703:5:assignment:column "c" does not exist Query: r[c+10] := 20 -- ^ Context: at assignment to variable "r" declared on line 2 (4 rows) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1() returns void as $$ declare r int; begin if false then r[10] := 20; end if; end; $$ language plpgsql set search_path = public; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------------------------------------------------------- error:42804:5:assignment:cannot subscript type integer because it does not support subscripting Query: r[10] := 20 -- ^ Context: at assignment to variable "r" declared on line 2 (4 rows) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1_trg() returns trigger as $$ begin if new.a > 10 then raise notice '%', new.b; raise notice '%', new.c; end if; return new; end; $$ language plpgsql; create trigger t1_f1 before insert on t1 for each row execute procedure f1_trg(); insert into t1 values(6,30); select * from plpgsql_check_function('f1_trg()','t1'); plpgsql_check_function --------------------------------------------------- error:42703:5:RAISE:record "new" has no field "c" Context: PL/pgSQL expression "new.c" (2 rows) insert into t1 values(6,30); create or replace function f1_trg() returns trigger as $$ begin new.a := new.a + 10; new.b := new.b + 10; new.c := 30; return new; end; $$ language plpgsql; -- should to fail select * from plpgsql_check_function('f1_trg()','t1'); plpgsql_check_function -------------------------------------------------------------------------- error:42703:5:assignment:record "new" has no field "c" Context: at assignment to field "c" of variable "new" declared on line 0 (2 rows) -- should to fail but not crash insert into t1 values(6,30); ERROR: record "new" has no field "c" CONTEXT: PL/pgSQL assignment "new.c := 30" PL/pgSQL function f1_trg() line 5 at assignment create or replace function f1_trg() returns trigger as $$ begin new.a := new.a + 10; new.b := new.b + 10; return new; end; $$ language plpgsql; -- ok select * from plpgsql_check_function('f1_trg()', 't1'); plpgsql_check_function ------------------------ (0 rows) -- ok insert into t1 values(6,30); select * from t1; a | b ----+---- 6 | 30 6 | 30 16 | 40 (3 rows) drop trigger t1_f1 on t1; drop function f1_trg(); -- test of showing caret on correct place for multiline queries create or replace function f1() returns void as $$ begin select var from foo; end; $$ language plpgsql; select * from plpgsql_check_function('f1()'); plpgsql_check_function ----------------------------------------------------------- error:42P01:3:SQL statement:relation "foo" does not exist Query: select var from foo -- ^ (6 rows) drop function f1(); create or replace function f1() returns int as $$ begin return (select a from t1 where hh = 20); end; $$ language plpgsql; select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------- error:42703:3:RETURN:column "hh" does not exist Query: (select a from t1 where hh = 20) -- ^ (5 rows) create or replace function f1() returns int as $$ begin return (select a from txxxxxxx where hh = 20); end; $$ language plpgsql; select * from plpgsql_check_function('f1()'); plpgsql_check_function --------------------------------------------------------- error:42P01:3:RETURN:relation "txxxxxxx" does not exist Query: (select a from txxxxxxx -- ^ where hh = 20) (5 rows) drop function f1(); drop table t1; -- raise warnings when target row has different number of attributies in -- SELECT INTO statement create or replace function f1() returns void as $$ declare a1 int; a2 int; begin select 10,20 into a1,a2; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('f1()'); plpgsql_check_function -------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "a1" warning extra:00000:2:DECLARE:never read variable "a2" (2 rows) create or replace function f1() returns void as $$ declare a1 int; begin select 10,20 into a1; end; $$ language plpgsql; -- raise warning select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------------------------------ warning:00000:4:SQL statement:too many attributes for target variables Query: select 10,20 -- ^ Detail: There are less target variables than output columns in query. Hint: Check target variables in SELECT INTO statement Context: at SQL statement to a1 variables warning extra:00000:2:DECLARE:never read variable "a1" (7 rows) create or replace function f1() returns void as $$ declare a1 int; a2 int; begin select 10 into a1,a2; end; $$ language plpgsql; -- raise warning select * from plpgsql_check_function('f1()'); plpgsql_check_function ----------------------------------------------------------------------- warning:00000:4:SQL statement:too few attributes for target variables Query: select 10 -- ^ Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. Context: at SQL statement to a1, a2 variables warning extra:00000:2:DECLARE:never read variable "a1" warning extra:00000:2:DECLARE:never read variable "a2" (8 rows) -- bogus code set check_function_bodies to off; create or replace function f1() returns void as $$ adasdfsadf $$ language plpgsql; select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------------------ error:42601:syntax error at or near "adasdfsadf" Query: adasdfsadf -- ^ Context: compilation of PL/pgSQL function "f1" near line 1 (6 rows) drop function f1(); create table f1tbl(a int, b int); -- unused variables create or replace function f1(_input1 int) returns table(_output1 int, _output2 int) as $$ declare _f1 int; _f2 int; _f3 int; _f4 int; _f5 int; _r record; _tbl f1tbl; begin if true then _f1 := 1; end if; select 1, 2 into _f3, _f4; perform 1 where _f5 is null; select 1 into _r; select 1, 2 into _tbl; -- check that SQLSTATE and SQLERRM don't raise false positives begin exception when raise_exception then end; end $$ language plpgsql; select * from plpgsql_check_function('f1(int)'); plpgsql_check_function ---------------------------------------------------------- warning:00000:4:DECLARE:unused variable "_f2" warning extra:00000:3:DECLARE:never read variable "_f1" warning extra:00000:5:DECLARE:never read variable "_f3" warning extra:00000:6:DECLARE:never read variable "_f4" warning extra:00000:8:DECLARE:never read variable "_r" warning extra:00000:9:DECLARE:never read variable "_tbl" warning extra:00000:unused parameter "_input1" warning extra:00000:unmodified OUT variable "_output1" warning extra:00000:unmodified OUT variable "_output2" (9 rows) drop function f1(int); drop table f1tbl; -- check that NEW and OLD are not reported unused create table f1tbl(); create or replace function f1() returns trigger as $$ begin return null; end $$ language plpgsql; select * from plpgsql_check_function('f1()', 'f1tbl'); plpgsql_check_function ------------------------ (0 rows) drop function f1(); drop table f1tbl; create table tabret(a int, b int); insert into tabret values(10,10); create or replace function f1() returns int as $$ begin return (select a from tabret); end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns int as $$ begin return (select a::numeric from tabret); end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:42804:3:RETURN:target type is different type than source type Detail: cast "numeric" value to "integer" type Hint: Hidden casting can be a performance issue. Context: at RETURN performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (6 rows) create or replace function f1() returns int as $$ begin return (select a, b from tabret); end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------- error:42601:3:RETURN:subquery must return only one column Query: (select a, b from tabret) -- ^ (3 rows) drop function f1(); create or replace function f1() returns table(ax int, bx int) as $$ begin return query select * from tabret; return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) drop function f1(); create or replace function f1() returns table(ax numeric, bx numeric) as $$ begin return query select * from tabret; return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------------- error:42804:3:RETURN QUERY:structure of query does not match function result type Detail: Returned type integer does not match expected type numeric in column "ax" (position 1). (2 rows) drop function f1(); create or replace function f1() returns setof tabret as $$ begin return query select * from tabret; return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns setof tabret as $$ begin return query select a from tabret; return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------- error:42804:3:RETURN QUERY:structure of query does not match function result type Detail: Number of returned columns (1) does not match expected column count (2). (2 rows) create or replace function f1() returns setof tabret as $$ begin return query select a::numeric,b::numeric from tabret; return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------------ error:42804:3:RETURN QUERY:structure of query does not match function result type Detail: Returned type numeric does not match expected type integer in column "a" (position 1). (2 rows) drop function f1(); create or replace function f1(a int) returns setof numeric as $$ begin return query select a; end $$ language plpgsql; select * from plpgsql_check_function('f1(int)', performance_warnings := true); plpgsql_check_function --------------------------------------------------------------------------------------------------------- error:42804:2:RETURN QUERY:structure of query does not match function result type Detail: Returned type integer does not match expected type numeric in column "__result__" (position 1). (2 rows) drop function f1(int); drop table tabret; create or replace function f1() returns void as $$ declare intval integer; begin intval := null; -- ok intval := 1; -- OK intval := '1'; -- OK intval := text '1'; -- not OK intval := current_date; -- not OK select 1 into intval; -- OK select '1' into intval; -- OK select text '1' into intval; -- not OK end $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function -------------------------------------------------------------------------------------- warning:42804:9:assignment:target type is different type than source type Query: intval := current_date -- ^ Detail: cast "date" value to "integer" type Hint: There are no possible explicit coercion between those types, possibly bug! Context: at assignment to variable "intval" declared on line 3 performance:00000:11:SQL statement:detected "SELECT expr INTO variable" Query: select 1 -- ^ Detail: This obsolete syntax of assigning can be slow. Hint: Use syntax "variable := expr" instead. performance:00000:12:SQL statement:detected "SELECT expr INTO variable" Query: select '1' -- ^ Detail: This obsolete syntax of assigning can be slow. Hint: Use syntax "variable := expr" instead. warning:42804:12:SQL statement:target type is different type than source type Query: select '1' -- ^ Detail: cast "text" value to "integer" type Hint: The input expression type does not have an assignment cast to the target type. Context: at SQL statement to variable "intval" declared on line 3 performance:00000:13:SQL statement:detected "SELECT expr INTO variable" Query: select text '1' -- ^ Detail: This obsolete syntax of assigning can be slow. Hint: Use syntax "variable := expr" instead. warning:42804:13:SQL statement:target type is different type than source type Query: select text '1' -- ^ Detail: cast "text" value to "integer" type Hint: The input expression type does not have an assignment cast to the target type. Context: at SQL statement to variable "intval" declared on line 3 warning extra:00000:3:DECLARE:never read variable "intval" (34 rows) drop function f1(); create or replace function f1() returns int as $$ begin return 1; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns int as $$ begin return 1::numeric; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:42804:3:RETURN:target type is different type than source type Detail: cast "numeric" value to "integer" type Hint: Hidden casting can be a performance issue. Context: at RETURN performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (6 rows) create or replace function f1() returns int as $$ begin return null; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns int as $$ begin return current_date; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- warning:42804:3:RETURN: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 performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (6 rows) create or replace function f1() returns int as $$ declare a int; begin return a; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns int as $$ declare a numeric; begin return a; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:42804:4:RETURN:target type is different type than source type Detail: cast "numeric" value to "integer" type Hint: Hidden casting can be a performance issue. Context: at RETURN of variable "a" declared on line 2 performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (6 rows) drop function f1(); create or replace function f1() returns setof int as $$ begin return next 1; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns setof int as $$ begin return next 1::numeric; -- tolerant, doesn't use tupmap end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:42804:3:RETURN NEXT:target type is different type than source type Detail: cast "numeric" value to "integer" type Hint: Hidden casting can be a performance issue. Context: at RETURN NEXT performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (6 rows) drop function f1(); create type t1 as (a int, b int, c int); create type t2 as (a int, b numeric); create or replace function fx() returns t2 as $$ declare x t1; begin return x; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------------ error:42804:4:RETURN:returned record type does not match expected record type Detail: Returned type integer does not match expected type numeric in column "b" (position 2). (2 rows) create or replace function fx() returns t2 as $$ declare x t2; begin return x; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) drop function fx(); create or replace function fx() returns setof t2 as $$ declare x t1; begin return next x; return; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------------ error:42804:4:RETURN NEXT:wrong record type supplied in RETURN NEXT Detail: Returned type integer does not match expected type numeric in column "b" (position 2). (2 rows) create or replace function fx() returns setof t2 as $$ declare x t2; begin return next x; return; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) drop function fx(); create or replace function fx2(_id int, _pa_id varchar(32), _status varchar(60)) returns void as $$ declare begin insert into pa values(_id, _pa_id, _status); exception when OTHERS then raise notice '%', 'some message'; raise exception '%', sqlerrm; end $$ language plpgsql; select * from plpgsql_check_function('fx2(int, varchar, varchar)', performance_warnings := true); plpgsql_check_function ------------------------ (0 rows) create or replace function fx2(_id int, _pa_id varchar(32), _status varchar(60)) returns void as $$ declare begin insert into pa values(_id, _pa_id, _status) returning *; exception when OTHERS then raise notice '%', 'some message'; raise exception '%', sqlerrm; end $$ language plpgsql; select * from plpgsql_check_function('fx2(int, varchar, varchar)', performance_warnings := true); plpgsql_check_function ---------------------------------------------------------------------- error:42601:4:SQL statement:query has no destination for result data (1 row) create or replace function fx2(_id int, _pa_id varchar(32), _status varchar(60)) returns void as $$ declare begin SELECT * FROM pa LIMIT 1; exception when OTHERS then raise notice '%', 'some message'; raise exception '%', sqlerrm; end $$ language plpgsql; select * from plpgsql_check_function('fx2(int, varchar, varchar)', performance_warnings := true); plpgsql_check_function ---------------------------------------------------------------------- error:42601:4:SQL statement:query has no destination for result data (1 row) drop function fx2(int, varchar, varchar); create or replace function foreach_array_loop() returns void as $body$ declare arr text[]; el text; begin arr := array['1111','2222','3333']; foreach el in array arr loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+-----------+----------+--------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- foreach_array_loop | | | 00000 | routine is marked as STABLE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function foreach_array_loop() returns void as $body$ declare arr text[]; el int; begin arr := array['1111','2222','3333']; foreach el in array arr loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+--------------------+----------+--------------------------------------------------+-------------------------------------+-----------------------------------------------------------------------------------+-------------+----------+-------+----------------------- foreach_array_loop | 7 | FOREACH over array | 42804 | target type is different type than source type | cast "text" value to "integer" type | The input expression type does not have an assignment cast to the target type. | warning | | | at FOREACH over array foreach_array_loop | | | 00000 | routine is marked as STABLE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (2 rows) create or replace function foreach_array_loop() returns void as $body$ declare arr date[]; el int; begin -- expression is not constant (else this check fails due tracking constant) arr := array['2014-01-01','2015-01-01','2016-01-01', current_date]::date[]; foreach el in array arr loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+--------------------+----------+------------------------------------------------+-------------------------------------+----------------------------------------------------------------------------+---------+----------+-------+----------------------- foreach_array_loop | 8 | FOREACH over array | 42804 | target type is different type than source type | cast "date" value to "integer" type | There are no possible explicit coercion between those types, possibly bug! | warning | | | at FOREACH over array (1 row) create or replace function foreach_array_loop() returns void as $body$ declare arr date[]; el int; begin arr := array['2014-01-01','2015-01-01','2016-01-01']::date[]; foreach el in array arr loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+--------------------+----------+-----------------------------------------------------+--------+------+-------+----------+-------+--------- foreach_array_loop | 7 | FOREACH over array | 22P02 | invalid input syntax for type integer: "01-01-2014" | | | error | | | (1 row) create or replace function foreach_array_loop() returns void as $body$ declare el text; begin foreach el in array array['1111','2222','3333'] loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+-----------+----------+--------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- foreach_array_loop | | | 00000 | routine is marked as STABLE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function foreach_array_loop() returns void as $body$ declare el int; begin foreach el in array array['1111','2222','3333'] loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+--------------------+----------+--------------------------------------------------+-------------------------------------+-----------------------------------------------------------------------------------+-------------+----------+-------+----------------------- foreach_array_loop | 5 | FOREACH over array | 42804 | target type is different type than source type | cast "text" value to "integer" type | The input expression type does not have an assignment cast to the target type. | warning | | | at FOREACH over array foreach_array_loop | | | 00000 | routine is marked as STABLE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (2 rows) create or replace function foreach_array_loop() returns void as $body$ declare el int; begin foreach el in array array['2014-01-01','2015-01-01','2016-01-01']::date[] loop raise notice '%', el; end loop; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('foreach_array_loop()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context --------------------+--------+--------------------+----------+-----------------------------------------------------+--------+------+-------+----------+-------+--------- foreach_array_loop | 5 | FOREACH over array | 22P02 | invalid input syntax for type integer: "01-01-2014" | | | error | | | (1 row) drop function foreach_array_loop(); create or replace function scan_rows(int[]) returns void AS $$ declare x int[]; begin foreach x slice 1 in array $1 loop raise notice 'row = %', x; end loop; end; $$ language plpgsql; select * from plpgsql_check_function_tb('scan_rows(int[])', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+----------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- scan_rows | | | 00000 | routine is marked as VOLATILE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function scan_rows(int[]) returns void AS $$ declare x int[]; begin foreach x in array $1 loop raise notice 'row = %', x; end loop; end; $$ language plpgsql; select * from plpgsql_check_function_tb('scan_rows(int[])', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+--------------------+----------+----------------------------------------------------+------------------------------------------+-----------------------------------------------------------------------------------+-------------+----------+-------+----------------------- scan_rows | 5 | FOREACH over array | 42804 | target type is different type than source type | cast "integer" value to "integer[]" type | There are no possible explicit coercion between those types, possibly bug! | warning | | | at FOREACH over array scan_rows | | | 00000 | routine is marked as VOLATILE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (2 rows) drop function scan_rows(int[]); drop function fx(); ERROR: function fx() does not exist drop type t1; drop type t2; create table t1(a int, b int); create table t2(a int, b int, c int); create table t3(a numeric, b int); insert into t1 values(10,20),(30,40); create or replace function fx() returns int as $$ declare s int default 0; r t1; begin foreach r in array (select array_agg(t1) from t1) loop s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function fx() returns int as $$ declare s int default 0; r t1; c t1[]; begin c := (select array_agg(t1) from t1); foreach r in array c loop s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function fx() returns int as $$ declare s int default 0; r t1; c t1[]; begin select array_agg(t1) into c from t1; foreach r in array c loop s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function fx() returns int as $$ declare s int default 0; r t1; c t1[]; begin select array_agg(t1) into c from t1; for i in array_lower(c, 1) .. array_upper(c, 1) loop r := c[i]; s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function fx() returns int as $$ declare s int default 0; c t1[]; begin select array_agg(t1) into c from t1; for i in array_lower(c, 1) .. array_upper(c, 1) loop s := (c[i]).a + (c[i]).b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function fx() returns int as $$ declare s int default 0; r record; c t1[]; begin select array_agg(t1) into c from t1; for i in array_lower(c, 1) .. array_upper(c, 1) loop r := c[i]; s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+--------- fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (1 row) create or replace function fx() returns int as $$ declare s int default 0; r record; c t1[]; begin select array_agg(t1) into c from t1; for i in array_lower(c, 1) .. array_upper(c, 1) loop r := c[i]; s := r.a + r.b + r.c; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+-----------------------------+--------+------+-------+----------+-------+-------------------------------------------- fx | 11 | assignment | 42703 | record "r" has no field "c" | | | error | | | PL/pgSQL assignment "s := r.a + r.b + r.c" (1 row) create or replace function fx() returns int as $$ declare s int default 0; r t2; begin foreach r in array (select array_agg(t1) from t1) loop s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+--------------------+----------+-------------------------------------------------+--------+-----------------------------------------------------------------------------------+-------------+----------+-------+----------------------- fx | 6 | FOREACH over array | 00000 | too few attributes for composite variable | | | warning | | | at FOREACH over array fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (2 rows) create or replace function fx() returns int as $$ declare s int default 0; r t3; begin foreach r in array (select array_agg(t1) from t1) loop s := r.a + r.b; end loop; return s; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+--------------------+----------+-------------------------------------------------+----------------------------------------+-----------------------------------------------------------------------------------+-------------+----------+----------------+-------------------------------------------------- fx | 6 | FOREACH over array | 42804 | target type is different type than source type | cast "integer" value to "numeric" type | Hidden casting can be a performance issue. | performance | | | at FOREACH over array fx | 8 | assignment | 42804 | target type is different type than source type | cast "numeric" value to "integer" type | Hidden casting can be a performance issue. | performance | 1 | s := r.a + r.b | at assignment to variable "s" declared on line 3 fx | | | 00000 | routine is marked as VOLATILE, should be STABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (3 rows) drop function fx(); drop table t1; -- mscottie issue #13 create table test ( a text, b integer, c uuid ); create function before_insert_test() returns trigger language plpgsql as $$ begin select a into NEW.a from test where b = 1; select b into NEW.b from test where b = 1; select null::uuid into NEW.c from test where b = 1; return new; end; $$; select * from plpgsql_check_function_tb('before_insert_test()','test'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) create or replace function before_insert_test() returns trigger language plpgsql as $$ begin NEW.a := (select a from test where b = 1); NEW.b := (select b from test where b = 1); NEW.c := (select c from test where b = 1); return new; end; $$; select * from plpgsql_check_function_tb('before_insert_test()','test', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) create or replace function before_insert_test() returns trigger language plpgsql as $$ begin NEW.a := 'Hello'::text; NEW.b := 10; NEW.c := null::uuid; return new; end; $$; select * from plpgsql_check_function_tb('before_insert_test()','test', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function before_insert_test(); create or replace function fx() returns void as $$ declare NEW test; OLD test; begin select null::uuid into NEW.c from test where b = 1; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true, fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------------------------+--------+------+---------------+----------+-------+--------- fx | 2 | DECLARE | 00000 | unused variable "old" | | | warning | | | fx | 2 | DECLARE | 00000 | never read variable "new" | | | warning extra | | | (2 rows) drop function fx(); create or replace function fx() returns void as $$ declare NEW test; begin NEW.a := 'Hello'::text; NEW.b := 10; NEW.c := null::uuid; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true, fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+----------------------------------------------------+--------+-----------------------------------------------------------------------------------+---------------+----------+-------+--------- fx | 2 | DECLARE | 00000 | never read variable "new" | | | warning extra | | | fx | | | 00000 | routine is marked as VOLATILE, should be IMMUTABLE | | When you fix this issue, please, recheck other functions that uses this function. | performance | | | (2 rows) drop function fx(); drop table test; create or replace function fx() returns void as $$ declare s int; sa int[]; sd date; bs int[]; begin sa[10] := s; sa[10] := sd; s := bs[10]; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx()', performance_warnings := true, fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+------------+----------+------------------------------------------------+-------------------------------------+----------------------------------------------------------------------------+---------------+----------+--------------+--------------------------------------------------- fx | 9 | assignment | 42804 | target type is different type than source type | cast "date" value to "integer" type | There are no possible explicit coercion between those types, possibly bug! | warning | 1 | sa[10] := sd | at assignment to variable "sa" declared on line 4 fx | 4 | DECLARE | 00000 | never read variable "sa" | | | warning extra | | | (2 rows) drop function fx(); create type t as (t text); create or replace function fx() returns void as $$ declare _t t; _tt t[]; _txt text; begin _t.t := 'ABC'; -- correct warning "unknown" _tt[1] := _t; _txt := _t; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------- performance:42804:7:assignment:target type is different type than source type Query: _txt := _t -- ^ Detail: cast "t" value to "text" type Hint: Hidden casting can be a performance issue. Context: at assignment to variable "_txt" declared on line 3 warning extra:00000:2:DECLARE:never read variable "_tt" warning extra:00000:3:DECLARE:never read variable "_txt" (8 rows) drop function fx(); create or replace function fx() returns void as $$ declare _t1 t; _t2 t; begin _t1.t := 'ABC'::text; _t2 := _t1; raise notice '% %', _t2, _t2.t; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) drop function fx(); create or replace function fx(out _tt t[]) as $$ declare _t t; begin _t.t := 'ABC'::text; _tt[1] := _t; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) drop function fx(); drop type t; create or replace function fx() returns int as $$ declare x int; begin perform 1; return 10; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- warning:00000:2:DECLARE:unused variable "x" performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (3 rows) drop function fx(); create table t(i int); create function test_t(OUT t) returns t AS $$ begin $1 := null; end; $$ language plpgsql; select test_t(); test_t -------- (1 row) select * from test_t(); i --- (1 row) select * from plpgsql_check_function('test_t()', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function fx() returns void as $$ declare c cursor for select * from t; x varchar; begin open c; fetch c into x; close c; end; $$ language plpgsql; select test_t(); test_t -------- (1 row) select * from test_t(); i --- (1 row) select * from plpgsql_check_function('fx()', performance_warnings := true, fatal_errors := false); plpgsql_check_function -------------------------------------------------------------------------- performance:42804:7:FETCH:target type is different type than source type Detail: cast "integer" value to "character varying" type Hint: Hidden casting can be a performance issue. Context: at FETCH to variable "x" declared on line 4 warning extra:00000:4:DECLARE:never read variable "x" (5 rows) drop function fx(); create or replace function fx() returns void as $$ declare c cursor for select * from t; x int; begin open c; fetch c into x; close c; end; $$ language plpgsql; select test_t(); test_t -------- (1 row) select * from test_t(); i --- (1 row) select * from plpgsql_check_function('fx()', performance_warnings := true, fatal_errors := false); plpgsql_check_function ------------------------------------------------------- warning extra:00000:4:DECLARE:never read variable "x" (1 row) drop function fx(); create or replace function fx() returns void as $$ declare c cursor for select * from t; begin for r in c loop raise notice '%', r.a; end loop; end; $$ language plpgsql; select test_t(); test_t -------- (1 row) select * from test_t(); i --- (1 row) select * from plpgsql_check_function('fx()', performance_warnings := true, fatal_errors := false); plpgsql_check_function ------------------------------------------------------- error:42703:6:RAISE:record "r" has no field "a" Context: PL/pgSQL expression "r.a" warning extra:00000:5:DECLARE:never read variable "r" (3 rows) drop function fx(); create or replace function fx() returns void as $$ declare c cursor for select * from t; begin for r in c loop raise notice '%', r.i; end loop; end; $$ language plpgsql; select test_t(); test_t -------- (1 row) select * from test_t(); i --- (1 row) select * from plpgsql_check_function('fx()', performance_warnings := true, fatal_errors := false); plpgsql_check_function ------------------------ (0 rows) drop function fx(); create table foo(a int, b int); create or replace function fx() returns void as $$ declare f1 int; f2 int; begin select 1, 2 into f1; select 1 into f1, f2; select a b into f1, f2 from foo; end; $$ language plpgsql; select fx(); fx ---- (1 row) select * from plpgsql_check_function('fx()', performance_warnings := true, fatal_errors := false); plpgsql_check_function ------------------------------------------------------------------------ warning:00000:4:SQL statement:too many attributes for target variables Query: select 1, 2 -- ^ Detail: There are less target variables than output columns in query. Hint: Check target variables in SELECT INTO statement Context: at SQL statement to f1 variables warning:00000:5:SQL statement:too few attributes for target variables Query: select 1 -- ^ Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. Context: at SQL statement to f1, f2 variables warning:00000:6:SQL statement:too few attributes for target variables Query: select a b from foo -- ^ Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. Context: at SQL statement to f1, f2 variables warning extra:00000:2:DECLARE:never read variable "f1" warning extra:00000:2:DECLARE:never read variable "f2" (20 rows) drop function fx(); drop table foo; create or replace function fx() returns void as $$ declare d date; begin d := (select 1 from pg_class limit 1); raise notice '%', d; end; $$ language plpgsql; select fx(); ERROR: invalid input syntax for type date: "1" CONTEXT: PL/pgSQL assignment "d := (select 1 from pg_class limit 1)" PL/pgSQL function fx() line 4 at assignment select * from plpgsql_check_function('fx()', performance_warnings := true, fatal_errors := false); plpgsql_check_function ---------------------------------------------------------------------------------- warning:42804:4:assignment:target type is different type than source type Query: d := (select 1 from pg_class limit 1) -- ^ Detail: cast "integer" value to "date" type Hint: There are no possible explicit coercion between those types, possibly bug! Context: at assignment to variable "d" declared on line 2 (6 rows) drop function fx(); create table tab_1(i int); create or replace function fx(a int) returns setof int as $$ declare c refcursor; r record; begin open c for select i from tab_1 where i = a; loop fetch c into r; if not found then exit; end if; return next r.i; end loop; end; $$ language plpgsql; select * from plpgsql_check_function('fx(int)', performance_warnings := true, fatal_errors := false); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function fx(a int) returns setof int as $$ declare c refcursor; r record; begin open c for select i from tab_1 where i = a; loop fetch c into r; if not found then exit; end if; return next r.x; end loop; end; $$ language plpgsql; select * from plpgsql_check_function('fx(int)', performance_warnings := true, fatal_errors := false); plpgsql_check_function ----------------------------------------------------------------------------------------- error:42703:12:RETURN NEXT:record "r" has no field "x" Context: PL/pgSQL expression "r.x" warning extra:00000:4:DECLARE:never read variable "r" performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (5 rows) drop function fx(int); drop table tab_1; create or replace function fxx() returns void as $$ begin rollback; end; $$ language plpgsql; select fxx(); ERROR: invalid transaction termination CONTEXT: PL/pgSQL function fxx() line 3 at ROLLBACK select * from plpgsql_check_function('fxx()'); plpgsql_check_function -------------------------------------------------------- error:2D000:3:ROLLBACK:invalid transaction termination (1 row) drop function fxx(); create or replace function fxx() returns void as $$ declare x int; begin declare x int; begin end; end; $$ language plpgsql; select * from plpgsql_check_function('fxx()'); plpgsql_check_function ------------------------------------------------------------------------------------------ warning extra:00000:5:statement block:variable "x" shadows a previously defined variable Hint: SET plpgsql.extra_warnings TO 'shadowed_variables' warning:00000:2:DECLARE:unused variable "x" warning:00000:4:DECLARE:unused variable "x" (4 rows) select * from plpgsql_check_function('fxx()', extra_warnings := false); plpgsql_check_function --------------------------------------------- warning:00000:2:DECLARE:unused variable "x" warning:00000:4:DECLARE:unused variable "x" (2 rows) drop function fxx(); create or replace function fxx(in a int, in b int, out c int, out d int) as $$ begin c := a; end; $$ language plpgsql; select * from plpgsql_check_function('fxx(int, int)'); plpgsql_check_function ------------------------------------------------- warning extra:00000:unused parameter "b" warning extra:00000:unmodified OUT variable "d" (2 rows) create or replace function fxx(in a int, in b int, out c int, out d int) as $$ begin c := d; end; $$ language plpgsql; select * from plpgsql_check_function('fxx(int, int)'); plpgsql_check_function ------------------------------------------------- warning extra:00000:unused parameter "a" warning extra:00000:unused parameter "b" warning extra:00000:unmodified OUT variable "d" (3 rows) create type ct as (a int, b int); create or replace function fxx(a ct, b ct, OUT c ct, OUT d ct) as $$ begin c.a := a.a; end; $$ language plpgsql; select * from plpgsql_check_function('fxx(ct, ct)'); plpgsql_check_function ----------------------------------------------------------------------- warning extra:00000:unused parameter "b" warning extra:00000:composite OUT variable "c" is not single argument warning extra:00000:composite OUT variable "d" is not single argument warning extra:00000:unmodified OUT variable "d" (4 rows) create or replace function fxx(a ct, b ct, OUT c ct, OUT d ct) as $$ begin c.a := d.a; end; $$ language plpgsql; select * from plpgsql_check_function('fxx(ct, ct)'); plpgsql_check_function ----------------------------------------------------------------------- warning extra:00000:unused parameter "a" warning extra:00000:unused parameter "b" warning extra:00000:composite OUT variable "c" is not single argument warning extra:00000:composite OUT variable "d" is not single argument warning extra:00000:unmodified OUT variable "d" (5 rows) create or replace function tx(a int) returns int as $$ declare a int; ax int; begin declare ax int; begin ax := 10; end; a := 10; return 20; end; $$ language plpgsql; select * from plpgsql_check_function('tx(int)'); plpgsql_check_function ------------------------------------------------------------------------------------------- warning:00000:3:statement block:parameter "a" is shadowed Detail: Local variable shadows function parameter. warning extra:00000:5:statement block:variable "ax" shadows a previously defined variable Hint: SET plpgsql.extra_warnings TO 'shadowed_variables' warning:00000:2:DECLARE:unused variable "ax" warning extra:00000:2:DECLARE:never read variable "a" warning extra:00000:4:DECLARE:never read variable "ax" warning extra:00000:unused parameter "a" (8 rows) create type xt as (a int, b int, c int); create or replace function fx_xt(out x xt) as $$ declare l xt; a int; begin return; end; $$ language plpgsql; select * from plpgsql_check_function('fx_xt()'); plpgsql_check_function ------------------------------------------------- warning:00000:2:DECLARE:unused variable "l" warning:00000:3:DECLARE:unused variable "a" warning extra:00000:unmodified OUT variable "x" (3 rows) drop function fx_xt(); create or replace function fx_xt(out x xt) as $$ declare l xt; a int; begin x.c := 1000; return; end; $$ language plpgsql; select * from plpgsql_check_function('fx_xt()'); plpgsql_check_function --------------------------------------------- warning:00000:2:DECLARE:unused variable "l" warning:00000:3:DECLARE:unused variable "a" (2 rows) drop function fx_xt(); create or replace function fx_xt(out x xt, out y xt) as $$ declare c1 xt; c2 xt; begin return; end; $$ language plpgsql; select * from plpgsql_check_function('fx_xt()'); plpgsql_check_function ----------------------------------------------------------------------- warning:00000:2:DECLARE:unused variable "c1" warning:00000:2:DECLARE:unused variable "c2" warning extra:00000:composite OUT variable "x" is not single argument warning extra:00000:unmodified OUT variable "x" warning extra:00000:composite OUT variable "y" is not single argument warning extra:00000:unmodified OUT variable "y" (6 rows) drop function fx_xt(); create or replace function fx_xt(out x xt, out y xt) as $$ declare c1 xt; c2 xt; begin x.a := 100; y := row(10,20,30); return; end; $$ language plpgsql; select * from plpgsql_check_function('fx_xt()'); plpgsql_check_function ----------------------------------------------------------------------- warning:00000:2:DECLARE:unused variable "c1" warning:00000:2:DECLARE:unused variable "c2" warning extra:00000:composite OUT variable "x" is not single argument warning extra:00000:composite OUT variable "y" is not single argument (4 rows) drop function fx_xt(); create or replace function fx_xt(out x xt, out z int) as $$ begin return; end; $$ language plpgsql; select * from plpgsql_check_function('fx_xt()'); plpgsql_check_function ----------------------------------------------------------------------- warning extra:00000:composite OUT variable "x" is not single argument warning extra:00000:unmodified OUT variable "x" warning extra:00000:unmodified OUT variable "z" (3 rows) drop function fx_xt(); drop type xt; -- missing RETURN create or replace function fx_flow() returns int as $$ begin raise notice 'kuku'; end; $$ language plpgsql; select fx_flow(); NOTICE: kuku ERROR: control reached end of function without RETURN CONTEXT: PL/pgSQL function fx_flow() select * from plpgsql_check_function('fx_flow()'); plpgsql_check_function ------------------------------------------------------------ error:2F005:control reached end of function without RETURN (1 row) -- ok create or replace function fx_flow() returns int as $$ declare a int; begin if a > 10 then return a; end if; return 10; end; $$ language plpgsql; select * from plpgsql_check_function('fx_flow()'); plpgsql_check_function ------------------------ (0 rows) -- dead code create or replace function fx_flow() returns int as $$ declare a int; begin if a > 10 then return a; else return a + 1; end if; return 10; end; $$ language plpgsql; select * from plpgsql_check_function('fx_flow()'); plpgsql_check_function ----------------------------------------------- warning extra:00000:9:RETURN:unreachable code (1 row) -- missing return create or replace function fx_flow() returns int as $$ declare a int; begin if a > 10 then return a; end if; end; $$ language plpgsql; select * from plpgsql_check_function('fx_flow()'); plpgsql_check_function -------------------------------------------------------------------- warning extra:2F005:control reached end of function without RETURN (1 row) drop function fx_flow(); create or replace function fx_flow(in p_param1 integer) returns text as $$ declare z1 text; begin if p_param1 is not null then z1 := '1111'; return z1; else z1 := '222222'; end if; return z1; end; $$ language plpgsql stable; select * from plpgsql_check_function_tb('fx_flow(integer)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) create or replace function fx_flow(in p_param1 integer) returns text as $$ declare z1 text; begin if p_param1 is not null then z1 := '1111'; return z1; else z1 := '222222'; raise exception 'stop'; end if; return z1; end; $$ language plpgsql stable; select * from plpgsql_check_function_tb('fx_flow(integer)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+------------------+--------+------+---------------+----------+-------+--------- fx_flow | 12 | RETURN | 00000 | unreachable code | | | warning extra | | | (1 row) drop function fx_flow(); ERROR: function fx_flow() does not exist drop function fx(int); ERROR: function fx(integer) does not exist create or replace function fx(x int) returns table(y int) as $$ begin return query select x union select x; end $$ language plpgsql; select * from fx(10); y ---- 10 (1 row) select * from plpgsql_check_function_tb('fx(int)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function fx(int); create or replace function fx(x int) returns table(y int, z int) as $$ begin return query select x,x+1 union select x, x+1; end $$ language plpgsql; select * from fx(10); y | z ----+---- 10 | 11 (1 row) select * from plpgsql_check_function_tb('fx(int)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function fx(int); create table xx(a int); create or replace function fx(x int) returns int as $$ declare _a int; begin begin select a from xx into strict _a where a = x; return _a; exception when others then null; end; return -1; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx(int)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop table xx; create or replace function fx(x int) returns int as $$ begin begin if (x > 0) then raise exception 'xxx' using errcode = 'XX888'; else raise exception 'yyy' using errcode = 'YY888'; end if; return -1; -- dead code; end; return -1; end; $$ language plpgsql; select * from plpgsql_check_function_tb('fx(int)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+------------------+--------+------+---------------+----------+-------+--------- fx | 9 | RETURN | 00000 | unreachable code | | | warning extra | | | fx | 11 | RETURN | 00000 | unreachable code | | | warning extra | | | (2 rows) create or replace function fx(x int) returns int as $$ begin begin if (x > 0) then raise exception 'xxx' using errcode = 'XX888'; else raise exception 'yyy' using errcode = 'YY888'; end if; exception when sqlstate 'XX888' then null; when sqlstate 'YY888' then null; end; end; -- missing return; $$ language plpgsql; select * from plpgsql_check_function_tb('fx(int)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+------------------------------------------------+--------+------+-------+----------+-------+--------- fx | | | 2F005 | control reached end of function without RETURN | | | error | | | (1 row) create or replace function fx(x int) returns int as $$ begin begin if (x > 0) then raise exception 'xxx' using errcode = 'XX888'; else raise exception 'yyy' using errcode = 'YY888'; end if; exception when others then return 10; end; end; -- ok now $$ language plpgsql; select * from plpgsql_check_function_tb('fx(int)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) --false alarm reported by Filip Zach create type testtype as (id integer); create or replace function fx() returns testtype as $$ begin return row(1); end; $$ language plpgsql; select * from fx(); id ---- 1 (1 row) select fx(); fx ----- (1) (1 row) select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------ (0 rows) drop function fx(); create function out1(OUT f1 int, OUT f2 int) returns setof record as $$ begin for f1, f2 in execute $q$ select 1, 2 $q$ loop return next; end loop; end $$ language plpgsql; select * from plpgsql_check_function('out1()'); plpgsql_check_function ------------------------ (0 rows) drop function out1(); create function out1(OUT f1 int, OUT f2 int) returns setof record as $$ begin for f1, f2 in select 1, 2 loop return next; end loop; end $$ language plpgsql; select * from plpgsql_check_function('out1()'); plpgsql_check_function ------------------------ (0 rows) drop function out1(); -- never read variable detection create function a() returns int as $$ declare foo int; begin foo := 2; return 1; end; $$ language plpgsql; select * from plpgsql_check_function('a()'); plpgsql_check_function --------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "foo" (1 row) drop function a(); -- issue #29 false unused variable create or replace function f1(in p_cursor refcursor) returns void as $body$ declare z_offset integer; begin z_offset := 10; move absolute z_offset from p_cursor; end; $body$ language 'plpgsql' stable; select * from plpgsql_check_function_tb('f1(refcursor)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function f1(refcursor); -- issue #30 segfault due NULL refname create or replace function test(a varchar) returns void as $$ declare x cursor (_a varchar) for select _a; begin open x(a); end; $$ language plpgsql; select * from plpgsql_check_function_tb('test(varchar)'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-------------------------+--------+------+---------------+----------+-------+--------- test | 2 | DECLARE | 00000 | never read variable "x" | | | warning extra | | | (1 row) drop function test(varchar); create or replace function test() returns void as $$ declare x numeric; begin x := NULL; end; $$ language plpgsql; select * from plpgsql_check_function('test()'); plpgsql_check_function ------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "x" (1 row) drop function test(); create table testtable(a int); create or replace function test() returns int as $$ declare r testtable; begin select * into r from testtable; return r.a; end; $$ language plpgsql; select * from plpgsql_check_function('test()'); plpgsql_check_function ------------------------ (0 rows) set check_function_bodies to on; drop table testtable; create table testtable(a int, b int); create or replace function test() returns int as $$ declare r testtable; begin select * into r from testtable; return r.a; end; $$ language plpgsql; alter table testtable drop column b; -- expected false alarm on PostgreSQL 10 and older -- there is not possibility to enforce recompilation -- before checking. select * from plpgsql_check_function('test()'); plpgsql_check_function ------------------------ (0 rows) drop function test(); -- issue #32 create table bigtable(id bigint, v varchar); create or replace function test() returns void as $$ declare r record; _id numeric; begin select * into r from bigtable where id = _id; for r in select * from bigtable where _id = id loop end loop; if (exists(select * from bigtable where id = _id)) then end if; end; $$ language plpgsql; select test(); test ------ (1 row) -- should to show performance warnings select * from plpgsql_check_function('test()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------------------------------------------------------------------- performance:42804:6:SQL statement:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause Query: select * from bigtable where id = _id -- ^ 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:7:FOR over SELECT rows:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause Query: select * from bigtable where _id = id -- ^ 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:IF:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause Query: (exists(select * from bigtable where id = _id)) -- ^ 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 warning extra:00000:3:DECLARE:never read variable "r" (16 rows) create or replace function test() returns void as $$ declare r record; _id bigint; begin select * into r from bigtable where id = _id; for r in select * from bigtable where _id = id loop end loop; if (exists(select * from bigtable where id = _id)) then end if; end; $$ language plpgsql; -- there are not any performance issue now select * from plpgsql_check_function('test()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------- warning extra:00000:3:DECLARE:never read variable "r" (1 row) -- nextval, currval and setval test create table test_table(); create or replace function testseq() returns void as $$ begin perform nextval('test_table'); perform currval('test_table'); perform setval('test_table', 10); perform setval('test_table', 10, true); end; $$ language plpgsql; -- should to fail select testseq(); ERROR: "test_table" is not a sequence CONTEXT: SQL statement "SELECT nextval('test_table')" PL/pgSQL function testseq() line 3 at PERFORM select * from plpgsql_check_function('testseq()', fatal_errors := false); plpgsql_check_function ------------------------------------------------------ error:42809:3:PERFORM:"test_table" is not a sequence Query: SELECT nextval('test_table') -- ^ error:42809:4:PERFORM:"test_table" is not a sequence Query: SELECT currval('test_table') -- ^ error:42809:5:PERFORM:"test_table" is not a sequence Query: SELECT setval('test_table', 10) -- ^ error:42809:6:PERFORM:"test_table" is not a sequence Query: SELECT setval('test_table', 10, true) -- ^ (12 rows) drop function testseq(); drop table test_table; -- tests designed for PostgreSQL 9.2 set check_function_bodies to off; create table t1(a int, b int); create function f1() returns void as $$ begin if false then update t1 set c = 30; end if; if false then raise notice '%', r.c; end if; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | (1 row) select * from plpgsql_check_function_tb('f1()', fatal_errors := false); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | f1 | 7 | RAISE | 42P01 | missing FROM-clause entry for table "r" | | | error | 1 | r.c | (2 rows) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+---------------+----------+--------------------------------------------+--------+------+-------+----------+----------------------+--------- f1 | 4 | SQL statement | 42703 | column "c" of relation "t1" does not exist | | | error | 15 | update t1 set c = 30 | (1 row) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1() returns void as $$ begin if false then raise notice '%', 1, 2; end if; end; $$ language plpgsql; select f1(); ERROR: too many parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "f1" near line 4 select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-----------------------------------------+--------+------+-------+----------+-------+--------------------------------------------------- f1 | | | 42601 | too many parameters specified for RAISE | | | error | | | compilation of PL/pgSQL function "f1" near line 4 (1 row) select f1(); ERROR: too many parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "f1" near line 4 drop function f1(); create or replace function f1() returns void as $$ begin if false then raise notice '% %'; end if; end; $$ language plpgsql; select f1(); ERROR: too few parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "f1" near line 4 select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+----------------------------------------+--------+------+-------+----------+-------+--------------------------------------------------- f1 | | | 42601 | too few parameters specified for RAISE | | | error | | | compilation of PL/pgSQL function "f1" near line 4 (1 row) select f1(); ERROR: too few parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "f1" near line 4 drop function f1(); create or replace function ml_trg() returns trigger as $$ #option dump declare begin if TG_OP = 'INSERT' then if NEW.status_from IS NULL then begin -- performance issue only select status into NEW.status_from from pa where pa_id = NEW.pa_id; -- nonexist target value select status into NEW.status_from_xxx from pa where pa_id = NEW.pa_id; exception when DATA_EXCEPTION then new.status_from := 'DE'; end; end if; end if; if TG_OP = 'DELETE' then return OLD; else return NEW; end if; exception when OTHERS then NULL; if TG_OP = 'DELETE' then return OLD; else return NEW; end if; end; $$ language plpgsql; select * from plpgsql_check_function('ml_trg()', 'ml', performance_warnings := true); plpgsql_check_function -------------------------------------------------------------------------- error:42703:13:SQL statement:record "new" has no field "status_from_xxx" (1 row) create or replace function fx2() returns void as $$ declare _pa pa; begin select pa.id into _pa.id from pa limit 1; select pa.pa_id into _pa.pa_id from pa limit 1; end; $$ language plpgsql; select * from plpgsql_check_function('fx2()', performance_warnings := true); plpgsql_check_function --------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "_pa" (1 row) drop function fx2(); create or replace function fx2() returns void as $$ declare _pa pa; begin _pa.id := (select pa.id from pa limit 1); _pa.pa_id := (select pa.pa_id from pa limit 1); end; $$ language plpgsql; select * from plpgsql_check_function('fx2()', performance_warnings := true); plpgsql_check_function --------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "_pa" (1 row) drop function fx2(); create or replace function test_lab() returns void as $$ begin <> for a in 1..3 loop <> BEGIN <> for b in 8..9 loop if a=2 then continue sub; end if; raise notice '% %', a, b; end loop inner; END sub; end loop outer; end; $$ language plpgsql; select test_lab(); ERROR: block label "sub" cannot be used in CONTINUE LINE 10: continue sub; ^ QUERY: begin <> for a in 1..3 loop <> BEGIN <> for b in 8..9 loop if a=2 then continue sub; end if; raise notice '% %', a, b; end loop inner; END sub; end loop outer; end; CONTEXT: compilation of PL/pgSQL function "test_lab" near line 10 select * from plpgsql_check_function('test_lab()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------- error:42601:block label "sub" cannot be used in CONTINUE Query: begin <> for a in 1..3 loop <> BEGIN <> for b in 8..9 loop if a=2 then continue sub; -- ^ end if; raise notice '% %', a, b; end loop inner; END sub; end loop outer; end; Context: compilation of PL/pgSQL function "test_lab" near line 10 (20 rows) create or replace function test_lab() returns void as $$ begin continue; end; $$ language plpgsql; select test_lab(); ERROR: CONTINUE cannot be used outside a loop LINE 3: continue; ^ QUERY: begin continue; end; CONTEXT: compilation of PL/pgSQL function "test_lab" near line 3 select * from plpgsql_check_function('test_lab()', performance_warnings := true); plpgsql_check_function ------------------------------------------------------------------ error:42601:CONTINUE cannot be used outside a loop Query: begin continue; -- ^ end; Context: compilation of PL/pgSQL function "test_lab" near line 3 (8 rows) create type _exception_type as ( state text, message text, detail text); create or replace function f1() returns void as $$ declare _exception record; begin _exception := NULL::_exception_type; exception when others then get stacked diagnostics _exception.state = RETURNED_SQLSTATE, _exception.message = MESSAGE_TEXT, _exception.detail = PG_EXCEPTION_DETAIL, _exception.hint = PG_EXCEPTION_HINT; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------------------------------------- error:42703:7:GET STACKED DIAGNOSTICS:record "_exception" has no field "hint" (1 row) drop function f1(); drop type _exception_type; drop table t1; create function myfunc1(a int, b float) returns integer as $$ begin end $$ language plpgsql; create function myfunc2(a int, b float) returns integer as $$ begin end $$ language plpgsql; create function myfunc3(a int, b float) returns integer as $$ begin end $$ language plpgsql; create function myfunc4(a int, b float) returns integer as $$ begin end $$ language plpgsql; create function opfunc1(a int, b float) returns integer as $$ begin end $$ language plpgsql; create operator *** (procedure = opfunc1, leftarg = int, rightarg = float); create table mytable(a int); create table myview as select * from mytable; create function testfunc(a int, b float) returns void as $$ declare x integer; begin raise notice '%', myfunc1(a, b); x := myfunc2(a, b) operator(public.***) 1; perform myfunc3(m.a, b) from myview m; insert into mytable select myfunc4(a, b); end; $$ language plpgsql; select * from plpgsql_check_function('testfunc(int,float)'); plpgsql_check_function ------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "x" (1 row) select type, schema, name, params from plpgsql_show_dependency_tb('testfunc(int,float)'); type | schema | name | params ----------+--------+---------+---------------------------- FUNCTION | public | myfunc1 | (integer,double precision) FUNCTION | public | myfunc2 | (integer,double precision) FUNCTION | public | myfunc3 | (integer,double precision) FUNCTION | public | myfunc4 | (integer,double precision) OPERATOR | public | *** | (integer,double precision) RELATION | public | mytable | RELATION | public | myview | (7 rows) drop function testfunc(int, float); drop function myfunc1(int, float); drop function myfunc2(int, float); drop function myfunc3(int, float); drop function myfunc4(int, float); drop table mytable; drop view myview; ERROR: "myview" is not a view HINT: Use DROP TABLE to remove a table. -- issue #34 create or replace function testcase() returns bool as $$ declare x int; begin set local search_path to public, test; case x when 1 then return true; else return false; end case; end; $$ language plpgsql; -- should not to raise warning select * from plpgsql_check_function('testcase()'); plpgsql_check_function ------------------------ (0 rows) drop function testcase(); -- Adam's Bartoszewicz example create or replace function public.test12() returns refcursor language plpgsql as $body$ declare rc refcursor; begin open rc scroll for select pc.* from pg_cast pc; return rc; end; $body$; -- should not returns false alarm select * from plpgsql_check_function('test12()'); plpgsql_check_function ------------------------ (0 rows) drop function public.test12(); -- should to show performance warning on bad flag create or replace function flag_test1(int) returns int as $$ begin return $1 + 10; end; $$ language plpgsql stable; create table fufu(a int); create or replace function flag_test2(int) returns int as $$ begin return (select * from fufu limit 1); end; $$ language plpgsql volatile; select * from plpgsql_check_function('flag_test1(int)', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as STABLE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) select * from plpgsql_check_function('flag_test2(int)', performance_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------------------- warning extra:00000:unused parameter "$1" performance:00000:routine is marked as VOLATILE, should be STABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (3 rows) drop table fufu; drop function flag_test1(int); drop function flag_test2(int); create or replace function rrecord01() returns setof record as $$ begin return query select 1,2; end; $$ language plpgsql; create or replace function rrecord02() returns record as $$ begin return row(10,20,30); end; $$ language plpgsql; create type record03 as (a int, b int); create or replace function rrecord03() returns record03 as $$ declare r record; begin r := row(1); return r; end; $$ language plpgsql; -- should not to raise false alarms select * from plpgsql_check_function('rrecord01'); plpgsql_check_function ------------------------ (0 rows) select * from plpgsql_check_function('rrecord02'); plpgsql_check_function ------------------------ (0 rows) -- should detect different return but still detect return select * from plpgsql_check_function('rrecord03', fatal_errors => false); plpgsql_check_function ---------------------------------------------------------------------------------- error:42804:5:RETURN:returned record type does not match expected record type Detail: Number of returned columns (1) does not match expected column count (2). (2 rows) drop function rrecord01(); drop function rrecord02(); drop function rrecord03(); drop type record03; create or replace function bugfunc01() returns void as $$ declare cvar cursor(a int, b int) for select a + b from generate_series(1,b); begin for t in cvar(1,3) loop raise notice '%', t; end loop; end; $$ language plpgsql; select bugfunc01(); NOTICE: (4) NOTICE: (4) NOTICE: (4) bugfunc01 ----------- (1 row) select * from plpgsql_check_function('bugfunc01'); plpgsql_check_function ------------------------ (0 rows) create or replace function bugfunc02() returns void as $$ declare cvar cursor(a int, b int) for select a + b from generate_series(1,b); begin open cvar(10,20); close cvar; end; $$ language plpgsql; select bugfunc02(); bugfunc02 ----------- (1 row) select * from plpgsql_check_function('bugfunc02'); plpgsql_check_function ------------------------ (0 rows) create or replace function bugfunc03() returns void as $$ declare cvar cursor(a int, b int) for select a + b from not_exists_table; begin open cvar(10,20); close cvar; end; $$ language plpgsql; select bugfunc03(); ERROR: relation "not_exists_table" does not exist LINE 1: select a + b from not_exists_table ^ QUERY: select a + b from not_exists_table CONTEXT: PL/pgSQL function bugfunc03() line 5 at OPEN select * from plpgsql_check_function('bugfunc03'); plpgsql_check_function --------------------------------------------------------------- error:42P01:5:OPEN:relation "not_exists_table" does not exist Query: select a + b from not_exists_table -- ^ (3 rows) create or replace function f1(out cr refcursor) as $$ begin end; $$ language plpgsql; -- should to raise warning select * from plpgsql_check_function('f1()'); plpgsql_check_function -------------------------------------------------- warning extra:00000:unmodified OUT variable "cr" (1 row) create or replace function f1(out cr refcursor) as $$ begin open cr for select 1; end; $$ language plpgsql; -- should not to raise warning, see issue #43 select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------ (0 rows) drop function f1(); create table testt(a int); create or replace function testt_trg_func() returns trigger as $$ begin return new; end; $$ language plpgsql; create trigger testt_trg before insert or update on testt for each row execute procedure testt_trg_func(); create or replace function maintaince_function() returns void as $$ begin alter table testt disable trigger testt_trg; alter table testt enable trigger testt_trg; end; $$ language plpgsql; -- should not to crash select * from plpgsql_check_function_tb('maintaince_function()', 0, true, true, true); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function maintaince_function(); drop trigger testt_trg on testt; drop function testt_trg_func(); drop table testt; create or replace function test_crash() returns void as $$ declare ec int default buggyfunc(10); begin select * into ec from buggytab; end; $$ language plpgsql; -- should not to crash select * from plpgsql_check_function('test_crash', fatal_errors := false); plpgsql_check_function ------------------------------------------------------------------------------ error:42883:4:statement block:function buggyfunc(integer) does not exist Query: buggyfunc(10) -- ^ Detail: There is no function of that name. Context: during statement block local variable "ec" initialization on line 3 error:42P01:5:SQL statement:relation "buggytab" does not exist Query: select * from buggytab -- ^ warning extra:00000:3:DECLARE:never read variable "ec" (9 rows) select * from plpgsql_check_function('test_crash', fatal_errors := true); plpgsql_check_function ------------------------------------------------------------------------------ error:42883:4:statement block:function buggyfunc(integer) does not exist Query: buggyfunc(10) -- ^ Detail: There is no function of that name. Context: during statement block local variable "ec" initialization on line 3 (5 rows) drop function test_crash(); -- fix false alarm reported by Piotr Stepniewski create or replace function public.fx() returns void language plpgsql as $function$ begin raise exception 'xxx'; end; $function$; -- show raise nothing select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------ (0 rows) create table errtab( message text, code character(5) ); create or replace function public.fx() returns void language plpgsql as $function$ declare var errtab%rowtype; begin raise exception using message = var.message, errcode = var.code; end; $function$; -- should not to crash select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------ (0 rows) create or replace function public.fx() returns void language plpgsql as $function$ declare var errtab%rowtype; begin raise exception using message = var.message, errcode = var.code, hint = var.hint; end; $function$; -- should not to crash select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------------------------------------ error:42703:5:RAISE:record "var" has no field "hint" Context: PL/pgSQL expression "var.hint" (2 rows) drop function fx(); create or replace function foo_format(a text, b text) returns void as $$ declare s text; begin s := format('%s'); -- should to raise error s := format('%s %10s', a, b); -- should be ok s := format('%s %s', a, b, a); -- should to raise warning s := format('%s %d', a, b); -- should to raise error raise notice '%', s; end; $$ language plpgsql; select * from plpgsql_check_function('foo_format', fatal_errors := false); plpgsql_check_function ------------------------------------------------------------------- error:22023:4:assignment:too few arguments for format() Query: s := format('%s') -- ^ Context: at assignment to variable "s" declared on line 2 warning:00000:6:assignment:unused parameters of function "format" Query: s := format('%s %s', a, b, a) -- ^ Context: at assignment to variable "s" declared on line 2 error:22023:7:assignment:unrecognized format() type specifier "d" Query: s := format('%s %d', a, b) -- ^ Context: at assignment to variable "s" declared on line 2 (12 rows) drop function foo_format(text, text); create or replace function dyn_sql_1() returns void as $$ declare v varchar; n int; begin execute 'select ' || n; -- ok execute 'select ' || quote_literal(v); -- ok execute 'select ' || v; -- vulnerable execute format('select * from %I', v); -- ok execute format('select * from %s', v); -- vulnerable execute 'select $1' using v; -- ok execute 'select 1'; -- ok execute 'select 1' using v; -- warning execute 'select $1'; -- error end; $$ language plpgsql; select * from plpgsql_check_function('dyn_sql_1', security_warnings := true, fatal_errors := false); plpgsql_check_function ------------------------------------------------------------------------------------------ security:00000:8:EXECUTE:text type variable is not sanitized Query: 'select ' || v -- ^ Detail: The EXECUTE expression is SQL injection vulnerable. Hint: Use quote_ident, quote_literal or format function to secure variable. security:00000:10:EXECUTE:text type variable is not sanitized Query: format('select * from %s', v) -- ^ Detail: The EXECUTE expression is SQL injection vulnerable. Hint: Use quote_ident, quote_literal or format function to secure variable. warning:00000:13:EXECUTE:values passed to EXECUTE statement by USING clause was not used error:42P02:14:EXECUTE:there is no parameter $1 Query: select $1 -- ^ (14 rows) drop function dyn_sql_1(); create type tp as (a int, b int); create or replace function dyn_sql_2() returns void as $$ declare r tp; result int; begin select 10 a, 20 b into r; raise notice '%', r.a; execute 'select $1.a + $1.b' into result using r; execute 'select $1.c' into result using r; -- error raise notice '%', result; end; $$ language plpgsql; select * from plpgsql_check_function('dyn_sql_2', security_warnings := true); plpgsql_check_function ------------------------------------------------------------ error:42703:9:EXECUTE:column "c" not found in data type tp Query: select $1.c -- ^ (3 rows) drop function dyn_sql_2(); drop type tp; /* * Should not to work * * note: plpgsql doesn't support passing some necessary details for record * type. The parser setup for dynamic SQL column doesn't use ref hooks, and * then it cannot to pass TupleDesc info to query anyway. */ create or replace function dyn_sql_2() returns void as $$ declare r record; result int; begin select 10 a, 20 b into r; raise notice '%', r.a; execute 'select $1.a + $1.b' into result using r; raise notice '%', result; end; $$ language plpgsql; select dyn_sql_2(); --should to fail NOTICE: 10 ERROR: could not identify column "a" in record data type LINE 1: select $1.a + $1.b ^ QUERY: select $1.a + $1.b CONTEXT: PL/pgSQL function dyn_sql_2() line 8 at EXECUTE select * from plpgsql_check_function('dyn_sql_2', security_warnings := true); plpgsql_check_function ------------------------------------------------------------------------- error:42703:8:EXECUTE:could not identify column "a" in record data type Query: select $1.a + $1.b -- ^ (3 rows) drop function dyn_sql_2(); create or replace function dyn_sql_3() returns void as $$ declare r int; begin execute 'select $1' into r using 1; raise notice '%', r; end $$ language plpgsql; select dyn_sql_3(); NOTICE: 1 dyn_sql_3 ----------- (1 row) -- should be ok select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ------------------------ (0 rows) create or replace function dyn_sql_3() returns void as $$ declare r record; begin execute 'select $1 as a, $2 as b' into r using 1, 2; raise notice '% %', r.a, r.b; end $$ language plpgsql; select dyn_sql_3(); NOTICE: 1 2 dyn_sql_3 ----------- (1 row) -- should be ok select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ------------------------ (0 rows) create or replace function dyn_sql_3() returns void as $$ declare r record; begin execute 'create table foo(a int)' into r using 1, 2; raise notice '% %', r.a, r.b; end $$ language plpgsql; -- raise a error select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ----------------------------------------------------------------------------------------- warning:00000:4:EXECUTE:values passed to EXECUTE statement by USING clause was not used error:XX000:4:EXECUTE:expression does not return data (2 rows) create or replace function dyn_sql_3() returns void as $$ declare r1 int; r2 int; begin execute 'select 1' into r1, r2 using 1, 2; raise notice '% %', r1, r2; end $$ language plpgsql; -- raise a error select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ----------------------------------------------------------------------------------------- warning:00000:4:EXECUTE:values passed to EXECUTE statement by USING clause was not used warning:00000:4:EXECUTE:too few attributes for target variables Query: select 1 -- ^ Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. Context: at EXECUTE to r1, r2 variables (7 rows) drop function dyn_sql_3(); create or replace function dyn_sql_3() returns void as $$ declare r record; begin for r in execute 'select 1 as a, 2 as b' loop raise notice '%', r.a; end loop; end $$ language plpgsql; -- should be ok select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ------------------------ (0 rows) drop function dyn_sql_3(); create or replace function dyn_sql_3() returns void as $$ declare r record; begin for r in execute 'select 1 as a, 2 as b' loop raise notice '%', r.c; end loop; end $$ language plpgsql; -- should be error select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ------------------------------------------------- error:42703:6:RAISE:record "r" has no field "c" Context: PL/pgSQL expression "r.c" (2 rows) drop function dyn_sql_3(); create or replace function dyn_sql_3() returns void as $$ declare r record; v text = 'select 10 a, 20 b't; begin for r in execute v loop raise notice '%', r.a; end loop; end $$ language plpgsql; -- should be ok select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function ------------------------ (0 rows) drop function dyn_sql_3(); create or replace function dyn_sql_4() returns table(ax int, bx int) as $$ begin return query execute 'select 10, 20'; return; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('dyn_sql_4()'); plpgsql_check_function ------------------------ (0 rows) create or replace function dyn_sql_4() returns table(ax int, bx int) as $$ begin return query execute 'select 10, 20, 30'; return; end; $$ language plpgsql; select * from dyn_sql_4(); ERROR: structure of query does not match function result type DETAIL: Number of returned columns (3) does not match expected column count (2). CONTEXT: SQL statement "select 10, 20, 30" PL/pgSQL function dyn_sql_4() line 3 at RETURN QUERY -- should be error select * from plpgsql_check_function('dyn_sql_4()'); plpgsql_check_function ----------------------------------------------------------------------------------- error:42804:3:RETURN QUERY:structure of query does not match function result type Detail: Number of returned columns (3) does not match expected column count (2). (2 rows) drop function dyn_sql_4(); create or replace function test_bug(text) returns regproc as $$ begin return $1::regproc; exception when undefined_function or invalid_name then raise; end; $$ language plpgsql; -- should not raise a exception select * from plpgsql_check_function('test_bug'); plpgsql_check_function ------------------------ (0 rows) create or replace function test_bug(text) returns regproc as $$ begin return $1::regproc; exception when undefined_function or invalid_name then raise notice '%', $1; -- bug end; $$ language plpgsql; select test_bug('kuku'); -- should to fail NOTICE: kuku ERROR: control reached end of function without RETURN CONTEXT: PL/pgSQL function test_bug(text) select * from plpgsql_check_function('test_bug'); plpgsql_check_function -------------------------------------------------------------------- warning extra:2F005:control reached end of function without RETURN (1 row) drop function test_bug(text); create or replace function test_bug(text) returns regproc as $$ begin return $1::regproc; exception when undefined_function or invalid_name then raise notice '%', $1; return NULL; end; $$ language plpgsql; select test_bug('kuku'); -- should be ok NOTICE: kuku test_bug ---------- (1 row) select * from plpgsql_check_function('test_bug'); plpgsql_check_function ------------------------ (0 rows) drop function test_bug(text); create or replace function foo(a text, b text) returns void as $$ begin -- unsecure execute 'select ' || a; a := quote_literal(a); -- is safe now execute 'select ' || a; a := a || b; -- it is unsecure again execute 'select ' || a; end; $$ language plpgsql; \sf+ foo(text, text) CREATE OR REPLACE FUNCTION public.foo(a text, b text) RETURNS void LANGUAGE plpgsql 1 AS $function$ 2 begin 3 -- unsecure 4 execute 'select ' || a; 5 a := quote_literal(a); -- is safe now 6 execute 'select ' || a; 7 a := a || b; -- it is unsecure again 8 execute 'select ' || a; 9 end; 10 $function$ -- should to raise two warnings select * from plpgsql_check_function('foo', security_warnings := true); plpgsql_check_function ----------------------------------------------------------------------------- security:00000:4:EXECUTE:text type variable is not sanitized Query: 'select ' || a -- ^ Detail: The EXECUTE expression is SQL injection vulnerable. Hint: Use quote_ident, quote_literal or format function to secure variable. security:00000:8:EXECUTE:text type variable is not sanitized Query: 'select ' || a -- ^ Detail: The EXECUTE expression is SQL injection vulnerable. Hint: Use quote_ident, quote_literal or format function to secure variable. (10 rows) drop function foo(text, text); create table testr(a int); create rule testr_rule as on insert to testr do nothing; create or replace function fx_testr() returns void as $$ begin insert into testr values(20); end; $$ language plpgsql; -- allow some rules on tables select fx_testr(); fx_testr ---------- (1 row) select * from plpgsql_check_function_tb('fx_testr'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) drop function fx_testr(); drop table testr; create or replace function f() returns void as $$ declare r1 record; r2 record; begin select 10 as a, 20 as b into r1; r2 := json_populate_record(r1, '{}'); raise notice '%', r2.a; end; $$ language plpgsql; select * from plpgsql_check_function('f'); plpgsql_check_function ------------------------ (0 rows) -- fix issue #63 create or replace function distinct_array(arr anyarray) returns anyarray as $$ begin return array(select distinct e from unnest(arr) as e); end; $$ language plpgsql immutable; select plpgsql_check_function('distinct_array(anyarray)'); plpgsql_check_function ------------------------ (0 rows) drop function distinct_array(anyarray); -- tracer test set plpgsql_check.enable_tracer to on; set plpgsql_check.tracer to on; set plpgsql_check.tracer_test_mode = true; \set VERBOSITY terse create or replace function fxo(a int, b int, c date, d numeric) returns void as $$ begin insert into tracer_tab values(a,b,c,d); end; $$ language plpgsql; create table tracer_tab(a int, b int, c date, d numeric); create or replace function tracer_tab_trg_fx() returns trigger as $$ begin return new; end; $$ language plpgsql; create trigger tracer_tab_trg before insert on tracer_tab for each row execute procedure tracer_tab_trg_fx(); select fxo(10,20,'20200815', 3.14); NOTICE: #0 ->> start of function fxo(integer,integer,date,numeric) (oid=0, tnl=1) NOTICE: #0 "a" => '10', "b" => '20', "c" => '08-15-2020', "d" => '3.14' NOTICE: #2 ->> start of function tracer_tab_trg_fx() (oid=0, tnl=1) NOTICE: #2 context: SQL statement "insert into tracer_tab values(a,b,c,d)" NOTICE: #2 triggered by before row insert trigger NOTICE: #2 "new" => '(10,20,08-15-2020,3.14)' NOTICE: #2 <<- end of function tracer_tab_trg_fx (elapsed time=0.010 ms) NOTICE: #0 <<- end of function fxo (elapsed time=0.010 ms) fxo ----- (1 row) select fxo(11,21,'20200816', 6.28); NOTICE: #0 ->> start of function fxo(integer,integer,date,numeric) (oid=0, tnl=1) NOTICE: #0 "a" => '11', "b" => '21', "c" => '08-16-2020', "d" => '6.28' NOTICE: #2 ->> start of function tracer_tab_trg_fx() (oid=0, tnl=1) NOTICE: #2 context: SQL statement "insert into tracer_tab values(a,b,c,d)" NOTICE: #2 triggered by before row insert trigger NOTICE: #2 "new" => '(11,21,08-16-2020,6.28)' NOTICE: #2 <<- end of function tracer_tab_trg_fx (elapsed time=0.010 ms) NOTICE: #0 <<- end of function fxo (elapsed time=0.010 ms) fxo ----- (1 row) set plpgsql_check.enable_tracer to off; set plpgsql_check.tracer to off; drop table tracer_tab cascade; drop function tracer_tab_trg_fx(); drop function fxo(int, int, date, numeric); create or replace function foo_trg_func() returns trigger as $$ begin -- bad function, RETURN is missing end; $$ language plpgsql; create table foo(a int); create trigger foo_trg before insert for each row execute procedure foo_trg_func(); ERROR: syntax error at or near "for" at character 38 -- should to print error select * from plpgsql_check_function('foo_trg_func', 'foo'); plpgsql_check_function ------------------------------------------------------------ error:2F005:control reached end of function without RETURN (1 row) drop table foo; drop function foo_trg_func(); -- check event trigger function create or replace function f1() returns event_trigger as $$ BEGIN RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; END $$ language plpgsql; select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) -- should fail create or replace function f1() returns event_trigger as $$ BEGIN RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tagX; END $$ language plpgsql; select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+---------------------------------+--------+------+-------+----------+---------+--------- f1 | 3 | RAISE | 42703 | column "tg_tagx" does not exist | | | error | 1 | tg_tagX | (1 row) drop function f1(); -- check event trigger function create or replace function f1() returns event_trigger as $$ BEGIN RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; END $$ language plpgsql; select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------ (0 rows) -- should fail create or replace function f1() returns event_trigger as $$ BEGIN RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tagX; END $$ language plpgsql; select * from plpgsql_check_function('f1()'); plpgsql_check_function ----------------------------------------------------- error:42703:3:RAISE:column "tg_tagx" does not exist Query: tg_tagX -- ^ (3 rows) drop function f1(); create table t1tab(a int, b int); create or replace function f1() returns setof t1tab as $$ begin return next (10,20); return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings => true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns setof t1tab as $$ begin return next (10::numeric,20); return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings => true); plpgsql_check_function ------------------------------------------------------------------------------------------------ error:42804:3:RETURN NEXT:returned record type does not match expected record type Detail: Returned type numeric does not match expected type integer in column "a" (position 1). (2 rows) create or replace function f1() returns setof t1tab as $$ declare a int; b int; begin return next (a,b); return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings => true); plpgsql_check_function ----------------------------------------------------------------------------------------- performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (2 rows) create or replace function f1() returns setof t1tab as $$ declare a numeric; b int; begin return next (a,b::numeric); return; end; $$ language plpgsql; select * from plpgsql_check_function('f1()', performance_warnings => true); plpgsql_check_function ------------------------------------------------------------------------------------------------ error:42804:4:RETURN NEXT:returned record type does not match expected record type Detail: Returned type numeric does not match expected type integer in column "a" (position 1). (2 rows) drop function f1(); create table t1(a int, b int); create or replace function fx() returns t2 as $$ begin return (10,20,30)::t1; end; $$ language plpgsql; select * from plpgsql_check_function('fx()', performance_warnings => true); plpgsql_check_function ---------------------------------------------------- error:42846:3:RETURN:cannot cast type record to t1 Query: (10,20,30)::t1 -- ^ Detail: Input has too many columns. (4 rows) drop function fx(); drop table t1tab; drop table t1; create or replace function fx() returns void as $$ begin assert exists(select * from foo); assert false, (select boo from boo limit 1); end; $$ language plpgsql; select * from plpgsql_check_function('fx()', fatal_errors => false); plpgsql_check_function ---------------------------------------------------- error:42P01:3:ASSERT:relation "foo" does not exist Query: exists(select * from foo) -- ^ error:42P01:4:ASSERT:relation "boo" does not exist Query: (select boo from boo limit 1) -- ^ (6 rows) create or replace function ml_trg() returns trigger as $$ #option dump declare begin if TG_OP = 'INSERT' then if NEW.status_from IS NULL then begin -- performance issue only select status into NEW.status_from from pa where pa_id = NEW.pa_id; -- nonexist target value select status into NEW.status_from_xxx from pa where pa_id = NEW.pa_id; exception when DATA_EXCEPTION then new.status_from := 'DE'; end; end if; end if; if TG_OP = 'DELETE' then return OLD; else return NEW; end if; exception when OTHERS then NULL; if TG_OP = 'DELETE' then return OLD; else return NEW; end if; end; $$ language plpgsql; select * from plpgsql_check_function('ml_trg()', 'ml', performance_warnings := true); plpgsql_check_function -------------------------------------------------------------------------- error:42703:13:SQL statement:record "new" has no field "status_from_xxx" (1 row) create or replace function fx2() returns void as $$ declare _pa pa; begin select pa.id into _pa.id from pa limit 1; select pa.pa_id into _pa.pa_id from pa limit 1; end; $$ language plpgsql; select * from plpgsql_check_function('fx2()', performance_warnings := true); plpgsql_check_function --------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "_pa" (1 row) drop function fx2(); create or replace function fx2() returns void as $$ declare _pa pa; begin _pa.id := (select pa.id from pa limit 1); _pa.pa_id := (select pa.pa_id from pa limit 1); end; $$ language plpgsql; select * from plpgsql_check_function('fx2()', performance_warnings := true); plpgsql_check_function --------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "_pa" (1 row) drop function fx2(); create type _exception_type as ( state text, message text, detail text); create or replace function f1() returns void as $$ declare _exception record; begin _exception := NULL::_exception_type; exception when others then get stacked diagnostics _exception.state = RETURNED_SQLSTATE, _exception.message = MESSAGE_TEXT, _exception.detail = PG_EXCEPTION_DETAIL, _exception.hint = PG_EXCEPTION_HINT; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-------------------------+----------+-----------------------------------------+--------+------+-------+----------+-------+--------- f1 | 7 | GET STACKED DIAGNOSTICS | 42703 | record "_exception" has no field "hint" | | | error | | | (1 row) create or replace function f1() returns void as $$ declare _exception _exception_type; begin _exception := NULL::_exception_type; exception when others then get stacked diagnostics _exception.state = RETURNED_SQLSTATE, _exception.message = MESSAGE_TEXT, _exception.detail = PG_EXCEPTION_DETAIL; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+----------------------------------+--------+------+---------------+----------+-------+--------- f1 | 3 | DECLARE | 00000 | never read variable "_exception" | | | warning extra | | | (1 row) drop function f1(); drop type _exception_type; create type _exception_type as ( state text, message text, detail text); create or replace function f1() returns void as $$ declare _exception record; begin _exception := NULL::_exception_type; exception when others then get stacked diagnostics _exception.state = RETURNED_SQLSTATE, _exception.message = MESSAGE_TEXT, _exception.detail = PG_EXCEPTION_DETAIL, _exception.hint = PG_EXCEPTION_HINT; end; $$ language plpgsql; select f1(); f1 ---- (1 row) select * from plpgsql_check_function('f1()'); plpgsql_check_function ------------------------------------------------------------------------------- error:42703:7:GET STACKED DIAGNOSTICS:record "_exception" has no field "hint" (1 row) drop function f1(); drop type _exception_type; create table footab(a int, b int, c int); create or replace function footab_trig_func() returns trigger as $$ declare x int; begin if false then -- should be ok; select count(*) from newtab into x; -- should fail; select count(*) from newtab where d = 10 into x; end if; return null; end; $$ language plpgsql; select * from plpgsql_check_function('footab_trig_func','footab', newtable := 'newtab'); plpgsql_check_function ------------------------------------------------------- error:42703:9:SQL statement:column "d" does not exist Query: select count(*) from newtab where d = 10 -- ^ (3 rows) drop table footab; drop function footab_trig_func(); create or replace function df1(anyelement) returns anyelement as $$ begin return $1; end; $$ language plpgsql; create or replace function df2(anyelement, jsonb) returns anyelement as $$ begin return $1; end; $$ language plpgsql; create or replace function t1() returns void as $$ declare r record; begin r := df1(r); end; $$ language plpgsql; select * from plpgsql_check_function('t1()'); plpgsql_check_function ------------------------ (0 rows) create or replace function t1() returns void as $$ declare r record; begin r := df2(r, '{}'); end; $$ language plpgsql; select * from plpgsql_check_function('t1()'); plpgsql_check_function ------------------------ (0 rows) create or replace function t1() returns void as $$ declare r1 record; r2 record; begin select 10 as a, 20 as b into r1; r2 := df1(r1); raise notice '%', r2.a; end; $$ language plpgsql; select * from plpgsql_check_function('t1()'); plpgsql_check_function ------------------------ (0 rows) create or replace function t1() returns void as $$ declare r1 record; r2 record; begin select 10 as a, 20 as b into r1; r2 := df2(r1, '{}'); raise notice '%', r2.a; end; $$ language plpgsql; select * from plpgsql_check_function('t1()'); plpgsql_check_function ------------------------ (0 rows) create or replace function df1(anyelement) returns anyelement as $$ select $1 $$ language sql; create or replace function df22(jsonb, anyelement) returns anyelement as $$ select $2; $$ language sql; create or replace function t1() returns void as $$ declare r1 record; r2 record; begin select 10 as a, 20 as b into r1; r2 := df1(r1); raise notice '%', r2.a; end; $$ language plpgsql; select * from plpgsql_check_function('t1()'); plpgsql_check_function ------------------------ (0 rows) create or replace function t1() returns void as $$ declare r1 record; r2 record; begin select 10 as a, 20 as b into r1; r2 := df22('{}', r1); raise notice '%', r2.a; end; $$ language plpgsql; select * from plpgsql_check_function('t1()'); plpgsql_check_function ------------------------ (0 rows) drop function df1(anyelement); drop function df2(anyelement, jsonb); drop function df22(jsonb, anyelement); drop function t1(); create or replace function dyntest() returns void as $$ begin execute 'drop table if exists xxx; create table xxx(a int)'; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('dyntest'); plpgsql_check_function ------------------------ (0 rows) create or replace function dyntest() returns void as $$ declare x int; begin execute 'drop table if exists xxx; create table xxx(a int)' into x; end; $$ language plpgsql; -- should to report error select * from plpgsql_check_function('dyntest'); plpgsql_check_function ------------------------------------------------------- error:XX000:4:EXECUTE:expression does not return data (1 row) drop function dyntest(); -- should to report error create type typ2 as (a int, b int); create or replace function broken_into() returns void as $$ declare v typ2; begin -- should to fail select (10,20)::typ2 into v; -- should be ok select ((10,20)::typ2).* into v; -- should to fail execute 'select (10,20)::typ2' into v; -- should be ok execute 'select ((10,20)::typ2).*' into v; end; $$ language plpgsql; select * from plpgsql_check_function('broken_into', fatal_errors => false); plpgsql_check_function ------------------------------------------------------------------------------------------------------------ error:42804:5:SQL statement:cannot cast composite value of "typ2" type to a scalar value of "integer" type Query: select (10,20)::typ2 -- ^ Context: at SQL statement to variable "v" declared on line 2 warning:00000:5:SQL statement:too few attributes for composite variable Query: select (10,20)::typ2 -- ^ Context: at SQL statement to variable "v" declared on line 2 error:42804:9:EXECUTE:cannot cast composite value of "typ2" type to a scalar value of "integer" type Context: at EXECUTE to variable "v" declared on line 2 warning:00000:9:EXECUTE:too few attributes for composite variable Context: at EXECUTE to variable "v" declared on line 2 warning extra:00000:2:DECLARE:never read variable "v" (13 rows) drop function broken_into(); drop type typ2; -- check output in xml or json formats CREATE OR REPLACE FUNCTION test_function() RETURNS void LANGUAGE plpgsql AS $function$ begin insert into non_existing_table values (1); end $function$; select * from plpgsql_check_function('test_function', format => 'xml'); plpgsql_check_function ---------------------------------------------------------------------------- + + error + 42P01 + relation "non_existing_table" does not exist + SQL statement + insert into non_existing_table values (1)+ + (1 row) select * from plpgsql_check_function('test_function', format => 'json'); plpgsql_check_function ----------------------------------------------------------------- { "issues":[ + { + "level":"error", + "message":"relation \"non_existing_table\" does not exist",+ "statement":{ + "lineNumber":"3", + "text":"SQL statement" + }, + "query":{ + "position":"13", + "text":"insert into non_existing_table values (1)" + }, + "sqlState":"42P01" + } + + ] + } (1 row) drop function test_function(); -- test settype pragma create or replace function test_function() returns void as $$ declare r record; begin raise notice '%', r.a; end; $$ language plpgsql; -- should to detect error select * from plpgsql_check_function('test_function'); plpgsql_check_function ---------------------------------------------------------------------------- error:55000:4:RAISE:record "r" is not assigned yet Detail: The tuple structure of a not-yet-assigned record is indeterminate. Context: PL/pgSQL expression "r.a" (3 rows) create type ctype as (a int, b int); create or replace function test_function() returns void as $$ declare r record; begin perform plpgsql_check_pragma('type: r ctype'); raise notice '%', r.a; end; $$ language plpgsql; -- should to be ok select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) create or replace function test_function() returns void as $$ <>declare r record; begin perform plpgsql_check_pragma('type: x.r public."ctype"'); raise notice '%', r.a; end; $$ language plpgsql; -- should to be ok select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) create or replace function test_function() returns void as $$ <>declare r record; begin perform plpgsql_check_pragma('type: "x".r (a int, b int)'); raise notice '%', r.a; end; $$ language plpgsql; -- should to be ok select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) create or replace function test_function() returns void as $$ <>declare r record; begin perform plpgsql_check_pragma('type: "x".r (a int, b int'); raise notice '%', r.a; end; $$ language plpgsql; -- should to be ok select * from plpgsql_check_function('test_function'); WARNING: Pragma "type" on line 4 is not processed. plpgsql_check_function ---------------------------------------------------------------------------- error:55000:5:RAISE:record "r" is not assigned yet Detail: The tuple structure of a not-yet-assigned record is indeterminate. Context: PL/pgSQL expression "r.a" (3 rows) create or replace function test_function() returns void as $$ <>declare r record; begin perform plpgsql_check_pragma('type: "x".r (a int, b int)x'); raise notice '%', r.a; end; $$ language plpgsql; -- should to be ok select * from plpgsql_check_function('test_function'); WARNING: Pragma "type" on line 4 is not processed. plpgsql_check_function ---------------------------------------------------------------------------- error:55000:5:RAISE:record "r" is not assigned yet Detail: The tuple structure of a not-yet-assigned record is indeterminate. Context: PL/pgSQL expression "r.a" (3 rows) drop function test_function(); drop type ctype; create or replace function test_function() returns void as $$ declare r pg_class; begin create temp table foo(like pg_class); select * from foo into r; end; $$ language plpgsql; -- should to raise an error select * from plpgsql_check_function('test_function'); plpgsql_check_function ----------------------------------------------------------- error:42P01:5:SQL statement:relation "foo" does not exist Query: select * from foo -- ^ (3 rows) create or replace function test_function() returns void as $$ declare r record; begin create temp table foo(like pg_class); perform plpgsql_check_pragma('table: foo(like pg_class)'); select * from foo into r; raise notice '%', r.relname; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) drop function test_function(); -- now plpgsql_check can do some other checks when statement EXECUTE -- contains only format function with constant fmt. create or replace function test_function() returns void as $$ begin execute format('create table zzz %I(a int, b int)', 'zzz'); end; $$ language plpgsql; -- should to detect bad expression select * from plpgsql_check_function('test_function'); plpgsql_check_function ----------------------------------------------------- error:42601:3:EXECUTE:syntax error at or near "zzz" (1 row) -- should to correctly detect type create or replace function test_function() returns void as $$ declare r record; begin execute format('select %L::date + 1 as x', current_date) into r; raise notice '%', extract(dow from r.x); end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) -- should not to crash create or replace function test_function() returns void as $$ declare r record; begin r := null; end; $$ language plpgsql; select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------------------------------------- warning extra:00000:2:DECLARE:never read variable "r" (1 row) drop function test_function(); -- ignores syntax errors when literals placehodlers are used create function test_function() returns void as $$ begin execute format('do %L', 'begin end'); end $$ language plpgsql; select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) drop function test_function(); load 'plpgsql_check'; drop type testtype cascade; create type testtype as (a int, b int); create function test_function() returns record as $$ declare r record; begin r := (10,20); if false then return r; end if; return null; end; $$ language plpgsql; create function test_function33() returns record as $$ declare r testtype; begin r := (10,20); if false then return r; end if; return null; end; $$ language plpgsql; -- should not to raise false alarm due check against fake result type select plpgsql_check_function('test_function'); plpgsql_check_function ------------------------ (0 rows) select plpgsql_check_function('test_function33'); plpgsql_check_function ------------------------ (0 rows) -- try to check in passive mode set plpgsql_check.mode = 'every_start'; select test_function(); test_function --------------- (1 row) select test_function33(); test_function33 ----------------- (1 row) select * from test_function() as (a int, b int); a | b ---+--- | (1 row) select * from test_function33() as (a int, b int); a | b ---+--- | (1 row) -- should to identify error select * from test_function() as (a int, b int, c int); ERROR: returned record type does not match expected record type select * from test_function33() as (a int, b int, c int); ERROR: returned record type does not match expected record type drop function test_function(); drop function test_function33(); drop type testtype; set plpgsql_check.mode to default; -- should not to raise false alarm create type c1 as ( a text ); create table t1 ( a c1, b c1 ); insert into t1 (values ('(abc)', '(def)')); alter table t1 drop column a; create or replace function test_function() returns t1 as $$ declare myrow t1%rowtype; begin select * into myrow from t1 limit 1; return myrow; end; $$ language plpgsql; select * from test_function(); b ------- (def) (1 row) select * from plpgsql_check_function('public.test_function()'); plpgsql_check_function ------------------------ (0 rows) drop function test_function(); drop table t1; drop type c1; -- compatibility warnings create or replace function foo01() returns refcursor as $$ declare c cursor for select 1; r refcursor; begin open c; r := c; return r; end; $$ language plpgsql; -- no warnings select * from plpgsql_check_function('foo01', compatibility_warnings => true); plpgsql_check_function ------------------------ (0 rows) create or replace function foo01() returns refcursor as $$ declare c cursor for select 1; r refcursor; begin open c; r := 'c'; return r; end; $$ language plpgsql; -- warning select * from plpgsql_check_function('foo01', extra_warnings => false, compatibility_warnings => true); plpgsql_check_function ----------------------------------------------------------------------------------- compatibility:00000:7:assignment:obsolete setting of refcursor or cursor variable Detail: Internal name of cursor should not be specified by users. Context: at assignment to variable "r" declared on line 4 (3 rows) create or replace function foo01() returns refcursor as $$ declare c cursor for select 1; r refcursor; begin open c; r := c; return 'c'; end; $$ language plpgsql; -- warning select * from plpgsql_check_function('foo01', extra_warnings => false, compatibility_warnings => true); plpgsql_check_function -------------------------------------------------------------------------------------- compatibility:00000:8:RETURN:obsolete setting of refcursor or cursor variable Detail: Internal name of cursor should not be specified by users. warning:42804:8: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 (6 rows) -- pragma sequence test create or replace function test_function() returns void as $$ begin perform plpgsql_check_pragma('sequence: xx'); perform nextval('pg_temp.xy'); perform nextval('pg_temp.xx'); end $$ language plpgsql; select * from plpgsql_check_function('test_function'); plpgsql_check_function ------------------------------------------------------------ error:42P01:4:PERFORM:relation "pg_temp.xy" does not exist Query: SELECT nextval('pg_temp.xy') -- ^ (3 rows) drop function test_function(); create table t1(x int); create or replace function f1_trg() returns trigger as $$ declare x int; begin return x; end; $$ language plpgsql; create trigger t1_f1 before insert on t1 for each row execute procedure f1_trg(); -- raise error select * from plpgsql_check_function('f1_trg', 't1'); plpgsql_check_function ----------------------------------------------------------------------------------------------- error:42804:4:RETURN:cannot return non-composite value from function returning composite type (1 row) drop trigger t1_f1 on t1; drop table t1; drop function f1_trg; create function test_function() returns void as $$ declare a int; b int; c int; d char; begin c := a + d; end; $$ language plpgsql; -- only b should be marked as unused variable select * from plpgsql_check_function('test_function', fatal_errors := false); plpgsql_check_function ----------------------------------------------------------------------- error:42883:6:assignment:operator does not exist: integer + character Query: c := a + d -- ^ Detail: No operator of that name accepts the given argument types. Hint: You might need to add explicit type casts. Context: at assignment to variable "c" declared on line 4 warning:00000:3:DECLARE:unused variable "b" warning extra:00000:4:DECLARE:never read variable "c" (8 rows) drop function test_function(); -- from plpgsql_check_active-12 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 at character 11 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 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: PL/pgSQL 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: PL/pgSQL 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, tnl=1) NOTICE: #0 "b" => '3' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0, tnl=1) NOTICE: #1 context: PL/pgSQL function 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, tnl=1) NOTICE: #1 context: PL/pgSQL function 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, tnl=1) NOTICE: #1 context: PL/pgSQL function 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, tnl=1) NOTICE: #0 "b" => '3' NOTICE: #0.1 3 --> start of statement block (tnl=1) NOTICE: #0.2 4 --> start of FOR with integer loop variable (tnl=1) NOTICE: #0.3 6 --> start of assignment r := nested_trace_test(r) (tnl=1) NOTICE: #0.3 "r" => '0' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0, tnl=1) NOTICE: #1 context: PL/pgSQL function trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '0' NOTICE: #1.1 2 --> start of statement block (tnl=1) NOTICE: #1.2 3 --> start of RETURN (expr='a + 1') (tnl=1) NOTICE: #1.2 "a" => '0' NOTICE: #1.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1.2 <-- end of statement block (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.3 "r" => '1' NOTICE: #0.3 6 --> start of assignment r := nested_trace_test(r) (tnl=1) NOTICE: #0.3 "r" => '1' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0, tnl=1) NOTICE: #1 context: PL/pgSQL function trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '1' NOTICE: #1.1 2 --> start of statement block (tnl=1) NOTICE: #1.2 3 --> start of RETURN (expr='a + 1') (tnl=1) NOTICE: #1.2 "a" => '1' NOTICE: #1.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1.2 <-- end of statement block (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.3 "r" => '2' NOTICE: #0.3 6 --> start of assignment r := nested_trace_test(r) (tnl=1) NOTICE: #0.3 "r" => '2' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0, tnl=1) NOTICE: #1 context: PL/pgSQL function trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '2' NOTICE: #1.1 2 --> start of statement block (tnl=1) NOTICE: #1.2 3 --> start of RETURN (expr='a + 1') (tnl=1) NOTICE: #1.2 "a" => '2' NOTICE: #1.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1.2 <-- end of statement block (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.3 "r" => '3' NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.4 8 --> start of RETURN (tnl=1) NOTICE: #0.4 "r" => '3' NOTICE: #0.3 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.4 <-- end of statement block (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, tnl=1) NOTICE: #0 "b" => '3' NOTICE: #0.1 3 --> start of statement block (tnl=1) NOTICE: #0.2 4 --> start of FOR with integer loop variable (tnl=1) NOTICE: #0.3 6 --> start of perform plpgsql_check_pragma('disable: .. (tnl=1) 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.5 9 --> start of RETURN (tnl=1) NOTICE: #0.5 "r" => '3' NOTICE: #0.4 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.5 <-- end of statement block (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, tnl=1) NOTICE: #0 "b" => '3' NOTICE: #0.1 3 --> start of statement block (tnl=1) NOTICE: #0.2 4 --> start of FOR with integer loop variable (tnl=1) NOTICE: #0.3 6 --> start of perform plpgsql_check_pragma('disable: .. (tnl=1) NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #1.3 4 --> start of RETURN (expr='a + 1') (tnl=1) NOTICE: #1.3 "a" => '0' NOTICE: #1.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1.3 4 --> start of RETURN (expr='a + 1') (tnl=1) NOTICE: #1.3 "a" => '1' NOTICE: #1.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1.3 4 --> start of RETURN (expr='a + 1') (tnl=1) NOTICE: #1.3 "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.5 9 --> start of RETURN (tnl=1) NOTICE: #0.5 "r" => '3' NOTICE: #0.4 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.5 <-- end of statement block (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, tnl=1) NOTICE: #0 "$1" => '4' NOTICE: #0.1 3 --> start of statement block (tnl=1) NOTICE: #0.2 4 --> start of FOR with integer loop variable (tnl=1) NOTICE: #0.3 5 --> start of assignment r := r + 1 (tnl=1) NOTICE: #0.3 "r" => '0' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.3 "r" => '1' NOTICE: #0.3 5 --> start of assignment r := r + 1 (tnl=1) NOTICE: #0.3 "r" => '1' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.3 "r" => '2' NOTICE: #0.3 5 --> start of assignment r := r + 1 (tnl=1) NOTICE: #0.3 "r" => '2' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.3 "r" => '3' NOTICE: #0.3 5 --> start of assignment r := r + 1 (tnl=1) NOTICE: #0.3 "r" => '3' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.3 "r" => '4' NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.4 7 --> start of assignment r := r + 10 (tnl=1) NOTICE: #0.4 "r" => '4' NOTICE: #0.3 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.4 "r" => '14' NOTICE: #0.5 8 --> start of RETURN (tnl=1) NOTICE: #0.5 "r" => '14' NOTICE: #0.4 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.5 <-- end of statement block (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, tnl=1) NOTICE: #0 "$1" => '4' NOTICE: #0.1 3 --> start of statement block (tnl=1) NOTICE: #0.2 4 --> start of FOR with integer loop variable (tnl=1) NOTICE: #0.3 5 --> start of perform plpgsql_check_pragma('disable: .. (tnl=1) 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.5 8 --> start of assignment r := r + 10 (tnl=1) NOTICE: #0.5 "r" => '4' NOTICE: #0.4 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.5 "r" => '14' NOTICE: #0.6 9 --> start of RETURN (tnl=1) NOTICE: #0.6 "r" => '14' NOTICE: #0.5 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.6 <-- end of statement block (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, tnl=1) NOTICE: #0 "$1" => '4' NOTICE: #0.1 3 --> start of statement block (tnl=1) NOTICE: #0.2 4 --> start of perform plpgsql_check_pragma('disable: .. (tnl=1) NOTICE: #0.1 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.6 12 --> start of assignment r := r + 10 (tnl=1) NOTICE: #0.6 "r" => '4' NOTICE: #0.5 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.6 "r" => '14' NOTICE: #0.7 13 --> start of RETURN (tnl=1) NOTICE: #0.7 "r" => '14' NOTICE: #0.6 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.7 <-- end of statement block (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 public.fx1() returns table(i integer, j integer, found boolean) as $$ begin for i in 1..10 loop for j in 1..10 loop return next; end loop; end loop; end; $$ language plpgsql immutable; select * from plpgsql_check_function('fx1'); plpgsql_check_function -------------------------------------------------------------------------- warning:00000:2:statement block:parameter "found" is shadowed Detail: Local auto variable shadows function parameter. warning:00000:3:FOR with integer loop variable:parameter "i" is shadowed Detail: Local auto variable shadows function parameter. warning:00000:5:FOR with integer loop variable:parameter "j" is shadowed Detail: Local auto variable shadows function parameter. warning extra:00000:unmodified OUT variable "i" warning extra:00000:unmodified OUT variable "j" warning extra:00000:unmodified OUT variable "found" (9 rows) drop function fx1; -- tracer test set plpgsql_check.enable_tracer to on; select plpgsql_check_tracer(true); NOTICE: tracer is active NOTICE: tracer verbosity is verbose plpgsql_check_tracer ---------------------- t (1 row) create role regress_plpgsql_check_test_role; DO $$ begin begin -- should to fail create role regress_plpgsql_check_test_role; exception when duplicate_object then -- Role already exists -- the exception handler is empty (#156) end; end; $$; NOTICE: #0 ->> start of block inline_code_block (oid=0, tnl=1) NOTICE: #0.1 2 --> start of statement block (tnl=1) NOTICE: #0.2 3 --> start of statement block (tnl=1) NOTICE: #0.3 5 --> start of SQL statement (query='create role regress_plpgsql_ch ..') (tnl=2) NOTICE: #0.1 <-- end of SQL statement (elapsed time=0.010 ms) aborted NOTICE: #0.2 <-- end of statement block (elapsed time=0.010 ms) NOTICE: #0.3 <-- end of statement block (elapsed time=0.010 ms) NOTICE: #0 <<- end of block (elapsed time=0.010 ms) drop role regress_plpgsql_check_test_role; set plpgsql_check.enable_tracer to off; select plpgsql_check_tracer(false); NOTICE: tracer is not active NOTICE: tracer verbosity is verbose plpgsql_check_tracer ---------------------- f (1 row) -- tracing constants -- issue #159 create or replace function tabret_dynamic() returns table (id integer, val text) as $$ begin return query execute 'select id, val from (values (1, ''a''), (2, ''b'')) as v(id, val)'; end; $$ language plpgsql immutable; -- should be ok select * from plpgsql_check_function('tabret_dynamic()'); plpgsql_check_function ------------------------ (0 rows) create or replace function tabret_dynamic() returns table (id integer, val text) as $$ declare z_query text; begin z_query := 'select id, val from (values (1, ''a''), (2, ''b'')) as v(id, val)'; execute z_query into id, val; return next; end; $$ language plpgsql immutable; -- should be ok select * from plpgsql_check_function('tabret_dynamic()'); plpgsql_check_function ------------------------ (0 rows) create or replace function tabret_dynamic() returns table (id integer, val text) as $$ declare z_query text; begin z_query := 'select id, val from (values (1, ''a''), (2, ''b'')) as v(id, val)'; for id, val in execute z_query loop return next; end loop; end; $$ language plpgsql immutable; -- should be ok select * from plpgsql_check_function('tabret_dynamic()'); plpgsql_check_function ------------------------ (0 rows) create or replace function tabret_dynamic() returns table (id integer, val text) as $$ declare z_query text; begin z_query := 'select id, val from (values (1, ''a''), (2, ''b'')) as v(id, val)'; return query execute z_query; end; $$ language plpgsql immutable; -- should be ok select * from plpgsql_check_function('tabret_dynamic()'); plpgsql_check_function ------------------------ (0 rows) drop function tabret_dynamic; -- should not to crash on empty string, or comment create or replace function dynamic_emptystr() returns void as $$ begin execute ''; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('dynamic_emptystr()'); plpgsql_check_function ------------------------ (0 rows) create or replace function dynamic_emptystr() returns void as $$ declare x int; begin execute '--' into x; end; $$ language plpgsql; -- should not to crash, no result error select * from plpgsql_check_function('dynamic_emptystr()'); plpgsql_check_function ------------------------------------------------------- error:XX000:4:EXECUTE:expression does not return data (1 row) drop function dynamic_emptystr(); -- unclosed cursor detection create or replace function fx() returns void as $$ declare c refcursor; begin open c for select * from pg_class; end; $$ language plpgsql; do $$ begin perform fx(); -- should to show warning perform fx(); end; $$; WARNING: cursor "c" is not closed set plpgsql_check.strict_cursors_leaks to on; do $$ begin -- should to show warning perform fx(); -- should to show warning perform fx(); end; $$; WARNING: cursor is not closed WARNING: cursor is not closed create or replace function fx() returns void as $$ declare c refcursor; begin open c for select * from pg_class; close c; end; $$ language plpgsql; -- without warnings do $$ begin perform fx(); perform fx(); end; $$; set plpgsql_check.strict_cursors_leaks to off; drop function fx(); create table public.testt(a int, b int); create or replace function fx() returns void as $$ declare v1 varchar default 'public'; v2 varchar default 'testt'; v3 varchar default 'a'; begin raise notice '%', format('%I.%I.%I', v1, v2, v3); perform 'pragma:assert-schema: v1'; perform 'pragma:assert-table: v1, v2'; perform 'pragma:assert-column: v1, v2, v3'; perform 'pragma:assert-table: v2'; perform 'pragma:assert-column: v2, v3'; end; $$ language plpgsql; select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------ (0 rows) create table public.testt(a int, b int); ERROR: relation "testt" already exists create or replace function fx() returns void as $$ declare v1 varchar default 'public'; v2 varchar default 'testt'; v3 varchar default 'x'; begin raise notice '%', format('%I.%I.%I', v1, v2, v3); perform 'pragma:assert-column: v1, v2, v3'; end; $$ language plpgsql; select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------------------------------------------------------------ error:42703:8:PERFORM:column "x" of relation "public"."testt" does not exist (1 row) drop function fx(); drop table public.testt; create or replace function fx() returns void as $$ declare v_sql varchar default ''; i int; begin -- we don't support constant tracing from queries select 'bla bla bla' into v_sql; execute v_sql into i; raise notice '%', i; end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------ (0 rows) drop function fx(); -- should not to raise warning do $$ declare c cursor for select * from generate_series(1,10); t int; begin for i in 1..2 loop open c; loop fetch c into t; exit when not found; raise notice '%', t; end loop; close c; end loop; end; $$; NOTICE: 1 NOTICE: 2 NOTICE: 3 NOTICE: 4 NOTICE: 5 NOTICE: 6 NOTICE: 7 NOTICE: 8 NOTICE: 9 NOTICE: 10 NOTICE: 1 NOTICE: 2 NOTICE: 3 NOTICE: 4 NOTICE: 5 NOTICE: 6 NOTICE: 7 NOTICE: 8 NOTICE: 9 NOTICE: 10 -- should not raise warning create or replace function fx(p text) returns void as $$ begin execute format($_$select '%1$I'$_$, p); end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('fx(text)'); plpgsql_check_function ------------------------ (0 rows) drop function fx(text); create or replace function fx() returns void as $$ declare p varchar; begin p := 'ahoj'; execute format($_$select '%1$I'$_$, p); end; $$ language plpgsql; -- should be ok select * from plpgsql_check_function('fx()'); plpgsql_check_function ------------------------ (0 rows) drop function fx(); -- should not crash create or replace procedure p1() as $$ begin commit; end; $$ language plpgsql; set plpgsql_check.cursors_leaks to on; do $$ declare c cursor for select 1; begin open c; call p1(); end $$; set plpgsql_check.cursors_leaks to default; drop procedure p1; -- should not crash 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; 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; -- when plpgsql_check is not loaded yet, then plpgsql_check is -- load by perform plpgsql_check_pragma and this is another -- case, when fmgr hook is not called in expected order. \c -- should not to crash select trace_test(3); trace_test ------------ 3 (1 row) create table testtable_pure_expr(a int); -- detection of not pure expressions create or replace function pure_expr() returns void as $$ declare v int; begin v := 1 delete from testtable_pure_expr where a = 10; raise notice '%', v; end; $$ language plpgsql; -- raise warning select * from plpgsql_check_function('pure_expr()'); plpgsql_check_function -------------------------------------------------------------------- warning extra:42601:4:assignment:expression is not pure expression Query: v := 1 -- ^ delete from testtable_pure_expr where a = 10 Detail: there is a possibility of unwanted behave Hint: Maybe you forgot to use a semicolon. Context: at assignment to variable "v" declared on line 2 (7 rows) drop table testtable_pure_expr; -- should not to raise false alarm drop function fxx(ct, ct); drop type ct; create type ct as (x int, y numeric, v varchar); create procedure proc_composite(inout cv ct, x int, y numeric, v varchar) as $$ begin cv.x := x; cv.y := y; cv.v := v; end; $$ language plpgsql; call proc_composite(null, 10, 10.2, 'ahoj'); cv ---------------- (10,10.2,ahoj) (1 row) create or replace function test_proc_composite() returns void as $$ declare cv ct; begin call proc_composite(cv, 10, 10.2, 'ahoj'); end; $$ language plpgsql; select * from plpgsql_check_function('test_proc_composite'); plpgsql_check_function ------------------------ (0 rows) drop function test_proc_composite; drop procedure proc_composite; create procedure proc_composite(inout cv1 ct, cv2 ct) as $$ begin cv1.x := 10; cv2.v := 'Ahoj'; end; $$ language plpgsql; create or replace function test_proc_composite() returns void as $$ declare cv1 ct; cv2 ct; begin call proc_composite(cv1, cv2); end; $$ language plpgsql; select * from plpgsql_check_function('test_proc_composite'); plpgsql_check_function ------------------------ (0 rows) drop function test_proc_composite; drop procedure proc_composite; drop type ct; create or replace function double_usage_of_const_str() returns void as $$ declare p_id_arr integer[]; r_id integer; begin p_id_arr := '{1,2,3}'::integer[]; foreach r_id in array p_id_arr loop raise notice 'id: %', r_id; end loop; --the following loop causes an error foreach r_id in array p_id_arr loop raise notice 'id: %', r_id; end loop; end; $$ language plpgsql; -- should not to raise false alarm #195 select * from plpgsql_check_function('double_usage_of_const_str'); plpgsql_check_function ------------------------ (0 rows) -- using reserved world for names is bad create or replace function test_bad_label() returns int as $$ <> declare x int = 10; begin return x; end; $$ language plpgsql; select * from plpgsql_check_function('test_bad_label'); plpgsql_check_function --------------------------------------------------------------------------- warning:00000:4:statement block:name of label "outer" is reserved keyword Detail: The reserved keyword was used as label name. (2 rows) drop function test_bad_label; -- using slow legacy syntax for assignment create or replace function test_bad_assign() returns int as $$ declare x int; begin select 0 into x; return x; end; $$ language plpgsql immutable; select * from plpgsql_check_function('test_bad_assign()', all_warnings=>true); plpgsql_check_function ------------------------------------------------------------------------ performance:00000:4:SQL statement:detected "SELECT expr INTO variable" Query: select 0 -- ^ Detail: This obsolete syntax of assigning can be slow. Hint: Use syntax "variable := expr" instead. (5 rows) drop function test_bad_assign; create or replace function volatility_check() returns int as $$ begin return (select count(*) from pg_class); end; $$ language plpgsql volatile; -- should be ok select * from plpgsql_check_function('volatility_check()'); plpgsql_check_function ------------------------ (0 rows) create or replace function volatility_check() returns int as $$ begin return (select count(*) from pg_class); end; $$ language plpgsql stable; -- should be ok select * from plpgsql_check_function('volatility_check()'); plpgsql_check_function ------------------------ (0 rows) create or replace function volatility_check() returns int as $$ begin return (select count(*) from pg_class); end; $$ language plpgsql immutable; -- warning select * from plpgsql_check_function('volatility_check()'); plpgsql_check_function ----------------------------------------------------------------------------------------- warning:00000:3:RETURN:routine is marked as IMMUTABLE, but expression is STABLE Detail: there is a risk of unwanted behave Hint: When you fix this issue, please, recheck other functions that uses this function. (3 rows) create or replace function volatility_check() returns int as $$ begin return random()*1000; end; $$ language plpgsql volatile; -- should be ok select * from plpgsql_check_function('volatility_check()'); plpgsql_check_function ------------------------ (0 rows) create or replace function volatility_check() returns int as $$ begin return random()*1000; end; $$ language plpgsql stable; -- warning select * from plpgsql_check_function('volatility_check()'); plpgsql_check_function ----------------------------------------------------------------------------------------- warning:00000:3:RETURN:routine is marked as STABLE, but expression is VOLATILE Detail: there is a risk of unwanted behave Hint: When you fix this issue, please, recheck other functions that uses this function. (3 rows) drop function volatility_check(); -- issue #201 - should not to raise false error, when composite -- constant is used and assigned to composite with more fields create type t_retcode as (retcode varchar, errmsg varchar, id int); create or replace function test_retcode_bad() returns void as $$ declare rc t_retcode := row('MO_REG_ERROR'); begin raise notice '%', rc.retcode; end; $$ language plpgsql; select * from plpgsql_check_function('test_retcode_bad'); plpgsql_check_function ------------------------------------------------------------------------------ warning:00000:4:statement block:too few attributes for composite variable Context: during statement block local variable "rc" initialization on line 3 (2 rows) drop function test_retcode_bad; -- test usage domain of composite in output #202 create domain t_retcode_domain as t_retcode; create or replace function test_retcode_bad(INOUT x1 t_retcode_domain, INOUT x2 int) as $$ begin x1.retcode = concat(x1.retcode, 'xxx'); x2 := x2 + 10; end; $$ language plpgsql; select * from plpgsql_check_function('test_retcode_bad'); plpgsql_check_function ------------------------------------------------------------------------ warning extra:00000:composite OUT variable "x1" is not single argument (1 row) drop function test_retcode_bad; drop type t_retcode cascade; NOTICE: drop cascades to type t_retcode_domain