LOAD 'plpgsql'; CREATE EXTENSION IF NOT EXISTS plpgsql_check; NOTICE: extension "plpgsql_check" already exists, skipping -- 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 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 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 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; -- should be moved to generic test 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(); /* * These function's cannot be executed in Postgres 9.5, because * Postgres there doesn't support plpgsql functions with record * type arguments. */ 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(); /* * 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 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: SQL 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) 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 r := 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 r := 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 r := 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 := 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 := 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 := 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 := 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 := 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 := 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 := 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);