LOAD 'plpgsql'; CREATE EXTENSION IF NOT EXISTS plpgsql_check; NOTICE: extension "plpgsql_check" already exists, skipping set client_min_messages to notice; create or replace procedure proc(a int) as $$ begin end; $$ language plpgsql; call proc(10); select * from plpgsql_check_function('proc(int)'); plpgsql_check_function ------------------------------------------ warning extra:00000:unused parameter "a" (1 row) create or replace procedure testproc() as $$ begin call proc(10); end; $$ language plpgsql; call testproc(); select * from plpgsql_check_function('testproc()'); plpgsql_check_function ------------------------ (0 rows) -- should to fail create or replace procedure testproc() as $$ begin call proc((select count(*) from pg_class)); end; $$ language plpgsql; call testproc(); ERROR: cannot use subquery in CALL argument LINE 1: CALL proc((select count(*) from pg_class)) ^ QUERY: CALL proc((select count(*) from pg_class)) CONTEXT: PL/pgSQL function testproc() line 3 at CALL select * from plpgsql_check_function('testproc()'); plpgsql_check_function --------------------------------------------------------- error:0A000:3:CALL:cannot use subquery in CALL argument Query: CALL proc((select count(*) from pg_class)) -- ^ (3 rows) drop procedure proc(int); create procedure proc(in a int, inout b int, in c int) as $$ begin end; $$ language plpgsql; select * from plpgsql_check_function('proc(int,int, int)'); plpgsql_check_function ------------------------------------------------- warning extra:00000:unused parameter "a" warning extra:00000:unused parameter "b" warning extra:00000:unused parameter "c" warning extra:00000:unmodified OUT variable "b" (4 rows) create or replace procedure proc(in a int, inout b int, in c int) as $$ begin b := a + c; end; $$ language plpgsql; select * from plpgsql_check_function('proc(int,int, int)'); plpgsql_check_function ------------------------ (0 rows) create or replace procedure testproc() as $$ declare r int; begin call proc(10, r, 20); end; $$ language plpgsql; call testproc(); select * from plpgsql_check_function('testproc()'); plpgsql_check_function ------------------------ (0 rows) -- should to fail create or replace procedure testproc() as $$ declare r int; begin call proc(10, r + 10, 20); end; $$ language plpgsql; call testproc(); ERROR: procedure parameter "b" is an output parameter but corresponding argument is not writable CONTEXT: PL/pgSQL function testproc() line 4 at CALL select * from plpgsql_check_function('testproc()'); plpgsql_check_function -------------------------------------------------------------------------------------------------------------- error:42601:4:CALL:procedure parameter "b" is an output parameter but corresponding argument is not writable (1 row) create or replace procedure testproc(inout r int) as $$ begin call proc(10, r, 20); end; $$ language plpgsql; call testproc(10); r ---- 30 (1 row) select * from plpgsql_check_function('testproc(int)'); plpgsql_check_function ------------------------ (0 rows) drop procedure testproc(int); -- should to raise warnings create or replace procedure testproc2(in p1 int, inout p2 int, in p3 int, inout p4 int) as $$ begin raise notice '% %', p1, p3; end; $$ language plpgsql; select * from plpgsql_check_function('testproc2'); plpgsql_check_function -------------------------------------------------- warning extra:00000:unused parameter "p2" warning extra:00000:unused parameter "p4" warning extra:00000:unmodified OUT variable "p2" warning extra:00000:unmodified OUT variable "p4" (4 rows) drop procedure testproc2; -- should be ok create or replace procedure testproc3(in p1 int, inout p2 int, in p3 int, inout p4 int) as $$ begin p2 := p1; p4 := p3; end; $$ language plpgsql; select * from plpgsql_check_function('testproc3'); plpgsql_check_function ------------------------ (0 rows) drop procedure testproc3; /* * Test pragma */ create or replace function test_pragma() returns void language plpgsql as $$ declare r record; begin perform plpgsql_check_pragma('disable:check'); raise notice '%', r.y; perform plpgsql_check_pragma('enable:check'); select 10 as a, 20 as b into r; raise notice '%', r.a; raise notice '%', r.x; end; $$; select * from plpgsql_check_function('test_pragma'); plpgsql_check_function ------------------------------------------------- error:42703:9:RAISE:record "r" has no field "x" Context: SQL statement "SELECT r.x" (2 rows) create or replace function test_pragma() returns void language plpgsql as $$ declare r record; begin if false then -- check is disabled just for if body perform plpgsql_check_pragma('disable:check'); raise notice '%', r.y; end if; select 10 as a, 20 as b into r; raise notice '%', r.a; raise notice '%', r.x; end; $$; select * from plpgsql_check_function('test_pragma'); plpgsql_check_function -------------------------------------------------- error:42703:11:RAISE:record "r" has no field "x" Context: SQL statement "SELECT r.x" (2 rows) drop function test_pragma(); create or replace function nested_trace_test(a int) returns int as $$ begin return a + 1; end; $$ language plpgsql; create or replace function trace_test(b int) returns int as $$ declare r int default 0; begin for i in 1..b loop r := nested_trace_test(r); end loop; return r; end; $$ language plpgsql; select trace_test(3); trace_test ------------ 3 (1 row) set plpgsql_check.enable_tracer to on; set plpgsql_check.tracer to on; set plpgsql_check.tracer_test_mode = true; select trace_test(3); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "b" => '3' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #1 call by trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '0' NOTICE: #1 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #1 call by trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '1' NOTICE: #1 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #1 call by trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '2' NOTICE: #1 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 3 (1 row) set plpgsql_check.tracer_verbosity TO verbose; select trace_test(3); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "b" => '3' NOTICE: #0.1 4 --> start of FOR with integer loop variable NOTICE: #0.2 6 --> start of assignment nested_trace_test(r) NOTICE: #0.2 "r" => '0' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #1 call by trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '0' NOTICE: #1.1 3 --> start of RETURN (expr='a + 1') NOTICE: #1.1 "a" => '0' NOTICE: #1.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '1' NOTICE: #0.2 6 --> start of assignment nested_trace_test(r) NOTICE: #0.2 "r" => '1' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #1 call by trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '1' NOTICE: #1.1 3 --> start of RETURN (expr='a + 1') NOTICE: #1.1 "a" => '1' NOTICE: #1.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '2' NOTICE: #0.2 6 --> start of assignment nested_trace_test(r) NOTICE: #0.2 "r" => '2' NOTICE: #1 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #1 call by trace_test(integer) line 6 at assignment NOTICE: #1 "a" => '2' NOTICE: #1.1 3 --> start of RETURN (expr='a + 1') NOTICE: #1.1 "a" => '2' NOTICE: #1.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #1 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '3' NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.3 8 --> start of RETURN NOTICE: #0.3 "r" => '3' NOTICE: #0.3 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 3 (1 row) create or replace function trace_test(b int) returns int as $$ declare r int default 0; begin for i in 1..b loop perform plpgsql_check_pragma('disable:tracer'); r := nested_trace_test(r); end loop; return r; end; $$ language plpgsql; select trace_test(3); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "b" => '3' NOTICE: #0.1 4 --> start of FOR with integer loop variable NOTICE: #0.2 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.2 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.2 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.4 9 --> start of RETURN NOTICE: #0.4 "r" => '3' NOTICE: #0.4 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 3 (1 row) create or replace function nested_trace_test(a int) returns int as $$ begin perform plpgsql_check_pragma('enable:tracer'); return a + 1; end; $$ language plpgsql; select trace_test(3); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "b" => '3' NOTICE: #0.1 4 --> start of FOR with integer loop variable NOTICE: #0.2 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #1.2 4 --> start of RETURN (expr='a + 1') NOTICE: #1.2 "a" => '0' NOTICE: #1.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.2 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #1.2 4 --> start of RETURN (expr='a + 1') NOTICE: #1.2 "a" => '1' NOTICE: #1.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.2 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #1.2 4 --> start of RETURN (expr='a + 1') NOTICE: #1.2 "a" => '2' NOTICE: #1.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.4 9 --> start of RETURN NOTICE: #0.4 "r" => '3' NOTICE: #0.4 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 3 (1 row) drop function trace_test(int); drop function nested_trace_test(int); create or replace function trace_test(int) returns int as $$ declare r int default 0; begin for i in 1..$1 loop r := r + 1; end loop; r := r + 10; return r; end; $$ language plpgsql; select trace_test(4); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "$1" => '4' NOTICE: #0.1 4 --> start of FOR with integer loop variable NOTICE: #0.2 5 --> start of assignment r + 1 NOTICE: #0.2 "r" => '0' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '1' NOTICE: #0.2 5 --> start of assignment r + 1 NOTICE: #0.2 "r" => '1' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '2' NOTICE: #0.2 5 --> start of assignment r + 1 NOTICE: #0.2 "r" => '2' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '3' NOTICE: #0.2 5 --> start of assignment r + 1 NOTICE: #0.2 "r" => '3' NOTICE: #0.2 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.2 "r" => '4' NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.3 7 --> start of assignment r + 10 NOTICE: #0.3 "r" => '4' NOTICE: #0.3 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.3 "r" => '14' NOTICE: #0.4 8 --> start of RETURN NOTICE: #0.4 "r" => '14' NOTICE: #0.4 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 14 (1 row) create or replace function trace_test(int) returns int as $$ declare r int default 0; begin for i in 1..$1 loop perform plpgsql_check_pragma('disable:tracer'); r := r + 1; end loop; r := r + 10; return r; end; $$ language plpgsql; select trace_test(4); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "$1" => '4' NOTICE: #0.1 4 --> start of FOR with integer loop variable NOTICE: #0.2 5 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.2 5 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.2 5 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.2 5 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.1 <-- end of FOR with integer loop variable (elapsed time=0.010 ms) NOTICE: #0.4 8 --> start of assignment r + 10 NOTICE: #0.4 "r" => '4' NOTICE: #0.4 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.4 "r" => '14' NOTICE: #0.5 9 --> start of RETURN NOTICE: #0.5 "r" => '14' NOTICE: #0.5 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 14 (1 row) create or replace function trace_test(int) returns int as $$ declare r int default 0; begin perform plpgsql_check_pragma('disable:tracer'); for i in 1..$1 loop r := r + 1; end loop; perform plpgsql_check_pragma('enable:tracer'); r := r + 10; return r; end; $$ language plpgsql; select trace_test(4); NOTICE: #0 ->> start of function trace_test(integer) (oid=0) NOTICE: #0 "$1" => '4' NOTICE: #0.1 4 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.1 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.5 12 --> start of assignment r + 10 NOTICE: #0.5 "r" => '4' NOTICE: #0.5 <-- end of assignment (elapsed time=0.010 ms) NOTICE: #0.5 "r" => '14' NOTICE: #0.6 13 --> start of RETURN NOTICE: #0.6 "r" => '14' NOTICE: #0.6 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #0 <<- end of function trace_test (elapsed time=0.010 ms) trace_test ------------ 14 (1 row) drop function trace_test(int);