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 | 8 | SELECT 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(); -- profiler check set plpgsql_check.profiler to on; 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; select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('f1()'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------- 1 | | | 2 | | | begin 3 | | | if false then 4 | | | insert into t1 values(10,20); 5 | | | update t1 set a = 10; 6 | | | delete from t1; 7 | | | end if; 8 | | | end; (8 rows) select f1(); f1 ---- (1 row) select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('f1()'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------- 1 | | | 2 | 2 | 1 | begin 3 | 3 | 1 | if false then 4 | 4 | 0 | insert into t1 values(10,20); 5 | 5 | 0 | update t1 set a = 10; 6 | 6 | 0 | delete from t1; 7 | | | end if; 8 | | | end; (8 rows) select plpgsql_profiler_reset('f1()'); plpgsql_profiler_reset ------------------------ (1 row) select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('f1()'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------- 1 | | | 2 | | | begin 3 | | | if false then 4 | | | insert into t1 values(10,20); 5 | | | update t1 set a = 10; 6 | | | delete from t1; 7 | | | end if; 8 | | | end; (8 rows) select f1(); f1 ---- (1 row) select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('f1()'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------- 1 | | | 2 | 2 | 1 | begin 3 | 3 | 1 | if false then 4 | 4 | 0 | insert into t1 values(10,20); 5 | 5 | 0 | update t1 set a = 10; 6 | 6 | 0 | delete from t1; 7 | | | end if; 8 | | | end; (8 rows) select plpgsql_profiler_reset_all(); plpgsql_profiler_reset_all ---------------------------- (1 row) select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('f1()'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------- 1 | | | 2 | | | begin 3 | | | if false then 4 | | | insert into t1 values(10,20); 5 | | | update t1 set a = 10; 6 | | | delete from t1; 7 | | | end if; 8 | | | end; (8 rows) drop function f1(); -- test queryid retrieval create function f1() returns void as $$ declare t1 text = 't1'; begin insert into t1 values(10,20); EXECUTE 'update ' || 't1' || ' set a = 10'; EXECUTE 'delete from ' || t1; end; $$ language plpgsql; select plpgsql_profiler_reset_all(); plpgsql_profiler_reset_all ---------------------------- (1 row) select plpgsql_profiler_install_fake_queryid_hook(); plpgsql_profiler_install_fake_queryid_hook -------------------------------------------- (1 row) select f1(); f1 ---- (1 row) select queryids, lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('f1()'); queryids | lineno | stmt_lineno | exec_stmts | source ----------+--------+-------------+------------+------------------------------------------------ | 1 | | | | 2 | | | declare | 3 | | | t1 text = 't1'; | 4 | 4 | 1 | begin {3} | 5 | 5 | 1 | insert into t1 values(10,20); {2} | 6 | 6 | 1 | EXECUTE 'update ' || 't1' || ' set a = 10'; {4} | 7 | 7 | 1 | EXECUTE 'delete from ' || t1; | 8 | | | end; (8 rows) select plpgsql_profiler_remove_fake_queryid_hook(); plpgsql_profiler_remove_fake_queryid_hook ------------------------------------------- (1 row) drop function f1(); set plpgsql_check.profiler to off; 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 | | | SQL statement "SELECT 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 | | | SQL statement "SELECT 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 | 8 | SELECT 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 | 8 | SELECT c+10 | at assignment to element of 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 | subscripted object is not an array | | | error | | | at assignment to element of 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 | | | SQL statement "SELECT 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 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 | 57 | SELECT (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 | 36 | SELECT (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 | | | 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 | | | 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: SQL statement "SELECT 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: SQL statement "SELECT 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: SELECT 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: SELECT c+10 -- ^ Context: at assignment to element of 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:subscripted object is not an array Context: at assignment to element of variable "r" declared on line 2 (2 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: SQL statement "SELECT 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 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 (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 (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 Detail: There are less target variables than output columns in query. Hint: Check target variables in SELECT INTO statement warning extra:00000:2:DECLARE:never read variable "a1" (4 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 Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. warning extra:00000:2:DECLARE:never read variable "a1" warning extra:00000:2:DECLARE:never read variable "a2" (5 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. 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) 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 (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 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 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 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:7:assignment:target type is different type than source type Detail: cast "text" value to "integer" type Hint: The input expression type does not have an assignment cast to the target type. Context: at assignment to variable "intval" declared on line 3 warning:42804:8:assignment:target type is different type than source type Detail: cast "text" value to "integer" type Hint: The input expression type does not have an assignment cast to the target type. Context: at assignment to variable "intval" declared on line 3 warning:42804:9:assignment: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 assignment to variable "intval" declared on line 3 warning:42804:12:SQL statement:target type is different type than source type Detail: cast "text" value to "integer" type Hint: The input expression type does not have an assignment cast to the target type. warning:42804:13:SQL statement:target type is different type than source type Detail: cast "text" value to "integer" type Hint: The input expression type does not have an assignment cast to the target type. warning extra:00000:3:DECLARE:never read variable "intval" (19 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. performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (5 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! 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) 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. performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (5 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. performance:00000:routine is marked as VOLATILE, should be IMMUTABLE Hint: When you fix this issue, please, recheck other functions that uses this function. (5 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 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 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 | | | 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 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 | 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 | | | 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 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 | | | 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 | 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 | | | 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) 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 | | | 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 | | | SQL statement "SELECT 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 | | | 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 | | | 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 | | | 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 | | | at assignment to element of variable "sa" declared on line 4 fx | 4 | DECLARE | 00000 | never read variable "sa" | | | 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 | | | (3 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 --------------------------------------------------------------------------------------------------- error:42804:7:assignment:cannot cast composite value of "t" type to a scalar value of "text" type Context: at assignment to variable "_txt" declared on line 3 (2 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. warning extra:00000:4:DECLARE:never read variable "x" (4 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: SQL statement "SELECT 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 Detail: There are less target variables than output columns in query. Hint: Check target variables in SELECT INTO statement warning:00000:5:SQL statement:too few attributes for target variables Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. warning:00000:6:SQL statement:too few attributes for target variables Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. warning extra:00000:2:DECLARE:never read variable "f1" warning extra:00000:2:DECLARE:never read variable "f2" (11 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 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 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 (4 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: SQL statement "SELECT 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 overlapped Detail: Local variable overlap 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: SELECT (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 | 8 | SELECT 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: SELECT buggyfunc(10) -- ^ Hint: No function matches the given name and argument types. You might need to add explicit type casts. 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: SELECT buggyfunc(10) -- ^ Hint: No function matches the given name and argument types. You might need to add explicit type casts. 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: SQL statement "SELECT 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: SELECT format('%s') -- ^ Context: at assignment to variable "s" declared on line 2 warning:00000:6:assignment:unused parameters of function "format" Query: SELECT 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: SELECT 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 '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: SELECT 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 Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. (4 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: SQL statement "SELECT 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 select 10 a, 20 b into r; for r in execute v loop raise notice '%', r.a; end loop; end $$ language plpgsql; -- should be warning select * from plpgsql_check_function('dyn_sql_3'); plpgsql_check_function --------------------------------------------------------------------------------------- warning:00000:7:FOR over EXECUTE statement:cannot determinate a result of dynamic SQL Detail: There is a risk of related false alarms. Hint: Don't use dynamic SQL and record type together, when you would check function. (3 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: 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 '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 '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); -- test of very long function inside profiler create or replace function longfx(int) returns int as $$ declare s int default 0; j int default 0; r record; begin begin while j < 10 loop for i in 1..1 loop for r in select * from generate_series(1,1) loop s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; s := s + 1; end loop; end loop; j := j + 1; end loop; exception when others then raise 'reraised exception %', sqlerrm; end; return $1; end; $$ language plpgsql; select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('longfx'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------------------------- 1 | | | 2 | | | declare 3 | | | s int default 0; 4 | | | j int default 0; 5 | | | r record; 6 | | | begin 7 | | | begin 8 | | | while j < 10 9 | | | loop 10 | | | for i in 1..1 11 | | | loop 12 | | | for r in select * from generate_series(1,1) 13 | | | loop 14 | | | s := s + 1; 15 | | | s := s + 1; 16 | | | s := s + 1; 17 | | | s := s + 1; 18 | | | s := s + 1; 19 | | | s := s + 1; 20 | | | s := s + 1; 21 | | | s := s + 1; 22 | | | s := s + 1; 23 | | | s := s + 1; 24 | | | s := s + 1; 25 | | | s := s + 1; 26 | | | s := s + 1; 27 | | | s := s + 1; 28 | | | s := s + 1; 29 | | | s := s + 1; 30 | | | s := s + 1; 31 | | | s := s + 1; 32 | | | s := s + 1; 33 | | | s := s + 1; 34 | | | s := s + 1; 35 | | | s := s + 1; 36 | | | s := s + 1; 37 | | | s := s + 1; 38 | | | s := s + 1; 39 | | | s := s + 1; 40 | | | s := s + 1; 41 | | | s := s + 1; 42 | | | s := s + 1; 43 | | | s := s + 1; 44 | | | s := s + 1; 45 | | | s := s + 1; 46 | | | s := s + 1; 47 | | | s := s + 1; 48 | | | s := s + 1; 49 | | | s := s + 1; 50 | | | s := s + 1; 51 | | | s := s + 1; 52 | | | s := s + 1; 53 | | | s := s + 1; 54 | | | s := s + 1; 55 | | | s := s + 1; 56 | | | s := s + 1; 57 | | | s := s + 1; 58 | | | s := s + 1; 59 | | | s := s + 1; 60 | | | s := s + 1; 61 | | | s := s + 1; 62 | | | s := s + 1; 63 | | | s := s + 1; 64 | | | s := s + 1; 65 | | | s := s + 1; 66 | | | s := s + 1; 67 | | | s := s + 1; 68 | | | s := s + 1; 69 | | | s := s + 1; 70 | | | s := s + 1; 71 | | | s := s + 1; 72 | | | s := s + 1; 73 | | | s := s + 1; 74 | | | s := s + 1; 75 | | | s := s + 1; 76 | | | s := s + 1; 77 | | | s := s + 1; 78 | | | s := s + 1; 79 | | | s := s + 1; 80 | | | s := s + 1; 81 | | | s := s + 1; 82 | | | s := s + 1; 83 | | | s := s + 1; 84 | | | s := s + 1; 85 | | | s := s + 1; 86 | | | s := s + 1; 87 | | | s := s + 1; 88 | | | s := s + 1; 89 | | | s := s + 1; 90 | | | s := s + 1; 91 | | | s := s + 1; 92 | | | s := s + 1; 93 | | | s := s + 1; 94 | | | s := s + 1; 95 | | | s := s + 1; 96 | | | s := s + 1; 97 | | | s := s + 1; 98 | | | s := s + 1; 99 | | | s := s + 1; 100 | | | s := s + 1; 101 | | | s := s + 1; 102 | | | s := s + 1; 103 | | | s := s + 1; 104 | | | s := s + 1; 105 | | | s := s + 1; 106 | | | s := s + 1; 107 | | | s := s + 1; 108 | | | s := s + 1; 109 | | | s := s + 1; 110 | | | s := s + 1; 111 | | | s := s + 1; 112 | | | s := s + 1; 113 | | | s := s + 1; 114 | | | s := s + 1; 115 | | | s := s + 1; 116 | | | s := s + 1; 117 | | | s := s + 1; 118 | | | s := s + 1; 119 | | | s := s + 1; 120 | | | s := s + 1; 121 | | | s := s + 1; 122 | | | s := s + 1; 123 | | | s := s + 1; 124 | | | s := s + 1; 125 | | | s := s + 1; 126 | | | s := s + 1; 127 | | | s := s + 1; 128 | | | s := s + 1; 129 | | | s := s + 1; 130 | | | s := s + 1; 131 | | | s := s + 1; 132 | | | s := s + 1; 133 | | | s := s + 1; 134 | | | s := s + 1; 135 | | | s := s + 1; 136 | | | s := s + 1; 137 | | | s := s + 1; 138 | | | s := s + 1; 139 | | | s := s + 1; 140 | | | s := s + 1; 141 | | | s := s + 1; 142 | | | s := s + 1; 143 | | | s := s + 1; 144 | | | s := s + 1; 145 | | | s := s + 1; 146 | | | s := s + 1; 147 | | | s := s + 1; 148 | | | s := s + 1; 149 | | | s := s + 1; 150 | | | s := s + 1; 151 | | | s := s + 1; 152 | | | s := s + 1; 153 | | | s := s + 1; 154 | | | s := s + 1; 155 | | | s := s + 1; 156 | | | s := s + 1; 157 | | | s := s + 1; 158 | | | s := s + 1; 159 | | | s := s + 1; 160 | | | s := s + 1; 161 | | | s := s + 1; 162 | | | s := s + 1; 163 | | | s := s + 1; 164 | | | s := s + 1; 165 | | | s := s + 1; 166 | | | s := s + 1; 167 | | | s := s + 1; 168 | | | s := s + 1; 169 | | | s := s + 1; 170 | | | s := s + 1; 171 | | | s := s + 1; 172 | | | s := s + 1; 173 | | | s := s + 1; 174 | | | s := s + 1; 175 | | | s := s + 1; 176 | | | s := s + 1; 177 | | | s := s + 1; 178 | | | s := s + 1; 179 | | | s := s + 1; 180 | | | s := s + 1; 181 | | | s := s + 1; 182 | | | s := s + 1; 183 | | | s := s + 1; 184 | | | s := s + 1; 185 | | | s := s + 1; 186 | | | s := s + 1; 187 | | | s := s + 1; 188 | | | s := s + 1; 189 | | | s := s + 1; 190 | | | s := s + 1; 191 | | | s := s + 1; 192 | | | s := s + 1; 193 | | | s := s + 1; 194 | | | s := s + 1; 195 | | | s := s + 1; 196 | | | s := s + 1; 197 | | | s := s + 1; 198 | | | s := s + 1; 199 | | | s := s + 1; 200 | | | s := s + 1; 201 | | | s := s + 1; 202 | | | s := s + 1; 203 | | | s := s + 1; 204 | | | s := s + 1; 205 | | | s := s + 1; 206 | | | s := s + 1; 207 | | | s := s + 1; 208 | | | s := s + 1; 209 | | | s := s + 1; 210 | | | s := s + 1; 211 | | | s := s + 1; 212 | | | s := s + 1; 213 | | | s := s + 1; 214 | | | s := s + 1; 215 | | | s := s + 1; 216 | | | s := s + 1; 217 | | | s := s + 1; 218 | | | s := s + 1; 219 | | | s := s + 1; 220 | | | s := s + 1; 221 | | | s := s + 1; 222 | | | s := s + 1; 223 | | | s := s + 1; 224 | | | s := s + 1; 225 | | | s := s + 1; 226 | | | s := s + 1; 227 | | | s := s + 1; 228 | | | s := s + 1; 229 | | | s := s + 1; 230 | | | s := s + 1; 231 | | | s := s + 1; 232 | | | s := s + 1; 233 | | | s := s + 1; 234 | | | s := s + 1; 235 | | | s := s + 1; 236 | | | s := s + 1; 237 | | | s := s + 1; 238 | | | s := s + 1; 239 | | | s := s + 1; 240 | | | s := s + 1; 241 | | | s := s + 1; 242 | | | s := s + 1; 243 | | | s := s + 1; 244 | | | s := s + 1; 245 | | | s := s + 1; 246 | | | s := s + 1; 247 | | | s := s + 1; 248 | | | s := s + 1; 249 | | | s := s + 1; 250 | | | s := s + 1; 251 | | | s := s + 1; 252 | | | s := s + 1; 253 | | | s := s + 1; 254 | | | s := s + 1; 255 | | | s := s + 1; 256 | | | s := s + 1; 257 | | | s := s + 1; 258 | | | s := s + 1; 259 | | | s := s + 1; 260 | | | s := s + 1; 261 | | | s := s + 1; 262 | | | s := s + 1; 263 | | | s := s + 1; 264 | | | s := s + 1; 265 | | | s := s + 1; 266 | | | s := s + 1; 267 | | | s := s + 1; 268 | | | s := s + 1; 269 | | | s := s + 1; 270 | | | s := s + 1; 271 | | | s := s + 1; 272 | | | s := s + 1; 273 | | | s := s + 1; 274 | | | s := s + 1; 275 | | | s := s + 1; 276 | | | s := s + 1; 277 | | | s := s + 1; 278 | | | s := s + 1; 279 | | | s := s + 1; 280 | | | s := s + 1; 281 | | | s := s + 1; 282 | | | s := s + 1; 283 | | | s := s + 1; 284 | | | s := s + 1; 285 | | | s := s + 1; 286 | | | s := s + 1; 287 | | | s := s + 1; 288 | | | s := s + 1; 289 | | | s := s + 1; 290 | | | s := s + 1; 291 | | | s := s + 1; 292 | | | s := s + 1; 293 | | | s := s + 1; 294 | | | s := s + 1; 295 | | | s := s + 1; 296 | | | s := s + 1; 297 | | | s := s + 1; 298 | | | s := s + 1; 299 | | | s := s + 1; 300 | | | s := s + 1; 301 | | | s := s + 1; 302 | | | s := s + 1; 303 | | | s := s + 1; 304 | | | s := s + 1; 305 | | | s := s + 1; 306 | | | s := s + 1; 307 | | | s := s + 1; 308 | | | s := s + 1; 309 | | | s := s + 1; 310 | | | s := s + 1; 311 | | | s := s + 1; 312 | | | s := s + 1; 313 | | | s := s + 1; 314 | | | s := s + 1; 315 | | | s := s + 1; 316 | | | s := s + 1; 317 | | | s := s + 1; 318 | | | s := s + 1; 319 | | | s := s + 1; 320 | | | s := s + 1; 321 | | | s := s + 1; 322 | | | s := s + 1; 323 | | | s := s + 1; 324 | | | s := s + 1; 325 | | | s := s + 1; 326 | | | s := s + 1; 327 | | | s := s + 1; 328 | | | s := s + 1; 329 | | | s := s + 1; 330 | | | s := s + 1; 331 | | | s := s + 1; 332 | | | s := s + 1; 333 | | | s := s + 1; 334 | | | s := s + 1; 335 | | | s := s + 1; 336 | | | s := s + 1; 337 | | | s := s + 1; 338 | | | s := s + 1; 339 | | | s := s + 1; 340 | | | s := s + 1; 341 | | | s := s + 1; 342 | | | s := s + 1; 343 | | | s := s + 1; 344 | | | s := s + 1; 345 | | | s := s + 1; 346 | | | s := s + 1; 347 | | | s := s + 1; 348 | | | s := s + 1; 349 | | | s := s + 1; 350 | | | s := s + 1; 351 | | | s := s + 1; 352 | | | s := s + 1; 353 | | | s := s + 1; 354 | | | s := s + 1; 355 | | | s := s + 1; 356 | | | s := s + 1; 357 | | | s := s + 1; 358 | | | s := s + 1; 359 | | | s := s + 1; 360 | | | s := s + 1; 361 | | | s := s + 1; 362 | | | s := s + 1; 363 | | | s := s + 1; 364 | | | s := s + 1; 365 | | | s := s + 1; 366 | | | s := s + 1; 367 | | | s := s + 1; 368 | | | s := s + 1; 369 | | | s := s + 1; 370 | | | s := s + 1; 371 | | | s := s + 1; 372 | | | s := s + 1; 373 | | | s := s + 1; 374 | | | s := s + 1; 375 | | | s := s + 1; 376 | | | s := s + 1; 377 | | | s := s + 1; 378 | | | s := s + 1; 379 | | | s := s + 1; 380 | | | s := s + 1; 381 | | | s := s + 1; 382 | | | s := s + 1; 383 | | | s := s + 1; 384 | | | s := s + 1; 385 | | | s := s + 1; 386 | | | s := s + 1; 387 | | | s := s + 1; 388 | | | s := s + 1; 389 | | | s := s + 1; 390 | | | s := s + 1; 391 | | | s := s + 1; 392 | | | s := s + 1; 393 | | | s := s + 1; 394 | | | s := s + 1; 395 | | | s := s + 1; 396 | | | s := s + 1; 397 | | | s := s + 1; 398 | | | s := s + 1; 399 | | | s := s + 1; 400 | | | s := s + 1; 401 | | | s := s + 1; 402 | | | s := s + 1; 403 | | | s := s + 1; 404 | | | s := s + 1; 405 | | | s := s + 1; 406 | | | s := s + 1; 407 | | | s := s + 1; 408 | | | s := s + 1; 409 | | | s := s + 1; 410 | | | s := s + 1; 411 | | | s := s + 1; 412 | | | s := s + 1; 413 | | | s := s + 1; 414 | | | s := s + 1; 415 | | | s := s + 1; 416 | | | s := s + 1; 417 | | | s := s + 1; 418 | | | s := s + 1; 419 | | | s := s + 1; 420 | | | s := s + 1; 421 | | | s := s + 1; 422 | | | s := s + 1; 423 | | | s := s + 1; 424 | | | s := s + 1; 425 | | | s := s + 1; 426 | | | s := s + 1; 427 | | | s := s + 1; 428 | | | s := s + 1; 429 | | | s := s + 1; 430 | | | s := s + 1; 431 | | | s := s + 1; 432 | | | s := s + 1; 433 | | | s := s + 1; 434 | | | s := s + 1; 435 | | | s := s + 1; 436 | | | s := s + 1; 437 | | | s := s + 1; 438 | | | s := s + 1; 439 | | | s := s + 1; 440 | | | s := s + 1; 441 | | | s := s + 1; 442 | | | s := s + 1; 443 | | | s := s + 1; 444 | | | s := s + 1; 445 | | | s := s + 1; 446 | | | s := s + 1; 447 | | | s := s + 1; 448 | | | s := s + 1; 449 | | | s := s + 1; 450 | | | s := s + 1; 451 | | | s := s + 1; 452 | | | s := s + 1; 453 | | | s := s + 1; 454 | | | s := s + 1; 455 | | | s := s + 1; 456 | | | s := s + 1; 457 | | | s := s + 1; 458 | | | s := s + 1; 459 | | | s := s + 1; 460 | | | s := s + 1; 461 | | | s := s + 1; 462 | | | s := s + 1; 463 | | | s := s + 1; 464 | | | s := s + 1; 465 | | | s := s + 1; 466 | | | s := s + 1; 467 | | | s := s + 1; 468 | | | s := s + 1; 469 | | | s := s + 1; 470 | | | s := s + 1; 471 | | | s := s + 1; 472 | | | s := s + 1; 473 | | | s := s + 1; 474 | | | s := s + 1; 475 | | | s := s + 1; 476 | | | s := s + 1; 477 | | | s := s + 1; 478 | | | s := s + 1; 479 | | | s := s + 1; 480 | | | s := s + 1; 481 | | | s := s + 1; 482 | | | s := s + 1; 483 | | | s := s + 1; 484 | | | s := s + 1; 485 | | | s := s + 1; 486 | | | s := s + 1; 487 | | | s := s + 1; 488 | | | s := s + 1; 489 | | | s := s + 1; 490 | | | s := s + 1; 491 | | | s := s + 1; 492 | | | s := s + 1; 493 | | | s := s + 1; 494 | | | s := s + 1; 495 | | | s := s + 1; 496 | | | s := s + 1; 497 | | | s := s + 1; 498 | | | s := s + 1; 499 | | | s := s + 1; 500 | | | s := s + 1; 501 | | | s := s + 1; 502 | | | s := s + 1; 503 | | | s := s + 1; 504 | | | s := s + 1; 505 | | | s := s + 1; 506 | | | s := s + 1; 507 | | | s := s + 1; 508 | | | s := s + 1; 509 | | | s := s + 1; 510 | | | s := s + 1; 511 | | | s := s + 1; 512 | | | s := s + 1; 513 | | | s := s + 1; 514 | | | s := s + 1; 515 | | | s := s + 1; 516 | | | s := s + 1; 517 | | | s := s + 1; 518 | | | s := s + 1; 519 | | | s := s + 1; 520 | | | s := s + 1; 521 | | | s := s + 1; 522 | | | s := s + 1; 523 | | | s := s + 1; 524 | | | s := s + 1; 525 | | | s := s + 1; 526 | | | s := s + 1; 527 | | | s := s + 1; 528 | | | s := s + 1; 529 | | | s := s + 1; 530 | | | s := s + 1; 531 | | | s := s + 1; 532 | | | s := s + 1; 533 | | | s := s + 1; 534 | | | s := s + 1; 535 | | | s := s + 1; 536 | | | s := s + 1; 537 | | | s := s + 1; 538 | | | s := s + 1; 539 | | | s := s + 1; 540 | | | s := s + 1; 541 | | | s := s + 1; 542 | | | s := s + 1; 543 | | | s := s + 1; 544 | | | s := s + 1; 545 | | | s := s + 1; 546 | | | s := s + 1; 547 | | | s := s + 1; 548 | | | s := s + 1; 549 | | | s := s + 1; 550 | | | s := s + 1; 551 | | | s := s + 1; 552 | | | s := s + 1; 553 | | | s := s + 1; 554 | | | s := s + 1; 555 | | | s := s + 1; 556 | | | s := s + 1; 557 | | | s := s + 1; 558 | | | s := s + 1; 559 | | | s := s + 1; 560 | | | s := s + 1; 561 | | | s := s + 1; 562 | | | s := s + 1; 563 | | | s := s + 1; 564 | | | s := s + 1; 565 | | | s := s + 1; 566 | | | s := s + 1; 567 | | | s := s + 1; 568 | | | s := s + 1; 569 | | | s := s + 1; 570 | | | s := s + 1; 571 | | | s := s + 1; 572 | | | s := s + 1; 573 | | | s := s + 1; 574 | | | s := s + 1; 575 | | | s := s + 1; 576 | | | s := s + 1; 577 | | | s := s + 1; 578 | | | s := s + 1; 579 | | | s := s + 1; 580 | | | s := s + 1; 581 | | | s := s + 1; 582 | | | s := s + 1; 583 | | | s := s + 1; 584 | | | s := s + 1; 585 | | | s := s + 1; 586 | | | s := s + 1; 587 | | | s := s + 1; 588 | | | s := s + 1; 589 | | | s := s + 1; 590 | | | s := s + 1; 591 | | | s := s + 1; 592 | | | s := s + 1; 593 | | | s := s + 1; 594 | | | s := s + 1; 595 | | | s := s + 1; 596 | | | s := s + 1; 597 | | | s := s + 1; 598 | | | s := s + 1; 599 | | | s := s + 1; 600 | | | s := s + 1; 601 | | | s := s + 1; 602 | | | s := s + 1; 603 | | | s := s + 1; 604 | | | s := s + 1; 605 | | | s := s + 1; 606 | | | s := s + 1; 607 | | | s := s + 1; 608 | | | s := s + 1; 609 | | | s := s + 1; 610 | | | s := s + 1; 611 | | | s := s + 1; 612 | | | s := s + 1; 613 | | | s := s + 1; 614 | | | s := s + 1; 615 | | | s := s + 1; 616 | | | s := s + 1; 617 | | | s := s + 1; 618 | | | s := s + 1; 619 | | | s := s + 1; 620 | | | s := s + 1; 621 | | | s := s + 1; 622 | | | s := s + 1; 623 | | | s := s + 1; 624 | | | s := s + 1; 625 | | | s := s + 1; 626 | | | s := s + 1; 627 | | | s := s + 1; 628 | | | s := s + 1; 629 | | | s := s + 1; 630 | | | s := s + 1; 631 | | | s := s + 1; 632 | | | s := s + 1; 633 | | | s := s + 1; 634 | | | s := s + 1; 635 | | | s := s + 1; 636 | | | s := s + 1; 637 | | | s := s + 1; 638 | | | s := s + 1; 639 | | | s := s + 1; 640 | | | s := s + 1; 641 | | | s := s + 1; 642 | | | s := s + 1; 643 | | | s := s + 1; 644 | | | s := s + 1; 645 | | | s := s + 1; 646 | | | s := s + 1; 647 | | | s := s + 1; 648 | | | s := s + 1; 649 | | | s := s + 1; 650 | | | s := s + 1; 651 | | | s := s + 1; 652 | | | s := s + 1; 653 | | | s := s + 1; 654 | | | s := s + 1; 655 | | | s := s + 1; 656 | | | s := s + 1; 657 | | | s := s + 1; 658 | | | s := s + 1; 659 | | | s := s + 1; 660 | | | s := s + 1; 661 | | | s := s + 1; 662 | | | s := s + 1; 663 | | | s := s + 1; 664 | | | s := s + 1; 665 | | | s := s + 1; 666 | | | s := s + 1; 667 | | | s := s + 1; 668 | | | s := s + 1; 669 | | | s := s + 1; 670 | | | s := s + 1; 671 | | | s := s + 1; 672 | | | s := s + 1; 673 | | | s := s + 1; 674 | | | s := s + 1; 675 | | | s := s + 1; 676 | | | s := s + 1; 677 | | | s := s + 1; 678 | | | s := s + 1; 679 | | | s := s + 1; 680 | | | s := s + 1; 681 | | | s := s + 1; 682 | | | s := s + 1; 683 | | | s := s + 1; 684 | | | s := s + 1; 685 | | | s := s + 1; 686 | | | s := s + 1; 687 | | | s := s + 1; 688 | | | s := s + 1; 689 | | | s := s + 1; 690 | | | s := s + 1; 691 | | | s := s + 1; 692 | | | s := s + 1; 693 | | | s := s + 1; 694 | | | s := s + 1; 695 | | | s := s + 1; 696 | | | s := s + 1; 697 | | | s := s + 1; 698 | | | s := s + 1; 699 | | | s := s + 1; 700 | | | s := s + 1; 701 | | | s := s + 1; 702 | | | s := s + 1; 703 | | | s := s + 1; 704 | | | s := s + 1; 705 | | | s := s + 1; 706 | | | s := s + 1; 707 | | | s := s + 1; 708 | | | s := s + 1; 709 | | | s := s + 1; 710 | | | s := s + 1; 711 | | | s := s + 1; 712 | | | s := s + 1; 713 | | | s := s + 1; 714 | | | s := s + 1; 715 | | | s := s + 1; 716 | | | s := s + 1; 717 | | | s := s + 1; 718 | | | s := s + 1; 719 | | | s := s + 1; 720 | | | s := s + 1; 721 | | | s := s + 1; 722 | | | s := s + 1; 723 | | | s := s + 1; 724 | | | s := s + 1; 725 | | | s := s + 1; 726 | | | s := s + 1; 727 | | | s := s + 1; 728 | | | s := s + 1; 729 | | | s := s + 1; 730 | | | s := s + 1; 731 | | | s := s + 1; 732 | | | s := s + 1; 733 | | | s := s + 1; 734 | | | s := s + 1; 735 | | | s := s + 1; 736 | | | s := s + 1; 737 | | | s := s + 1; 738 | | | s := s + 1; 739 | | | s := s + 1; 740 | | | s := s + 1; 741 | | | s := s + 1; 742 | | | s := s + 1; 743 | | | s := s + 1; 744 | | | s := s + 1; 745 | | | s := s + 1; 746 | | | s := s + 1; 747 | | | s := s + 1; 748 | | | s := s + 1; 749 | | | s := s + 1; 750 | | | s := s + 1; 751 | | | s := s + 1; 752 | | | s := s + 1; 753 | | | s := s + 1; 754 | | | s := s + 1; 755 | | | s := s + 1; 756 | | | s := s + 1; 757 | | | s := s + 1; 758 | | | s := s + 1; 759 | | | s := s + 1; 760 | | | s := s + 1; 761 | | | s := s + 1; 762 | | | s := s + 1; 763 | | | s := s + 1; 764 | | | s := s + 1; 765 | | | s := s + 1; 766 | | | s := s + 1; 767 | | | s := s + 1; 768 | | | s := s + 1; 769 | | | s := s + 1; 770 | | | s := s + 1; 771 | | | s := s + 1; 772 | | | s := s + 1; 773 | | | s := s + 1; 774 | | | s := s + 1; 775 | | | s := s + 1; 776 | | | s := s + 1; 777 | | | s := s + 1; 778 | | | s := s + 1; 779 | | | s := s + 1; 780 | | | s := s + 1; 781 | | | s := s + 1; 782 | | | s := s + 1; 783 | | | s := s + 1; 784 | | | s := s + 1; 785 | | | s := s + 1; 786 | | | s := s + 1; 787 | | | s := s + 1; 788 | | | s := s + 1; 789 | | | s := s + 1; 790 | | | s := s + 1; 791 | | | s := s + 1; 792 | | | s := s + 1; 793 | | | s := s + 1; 794 | | | s := s + 1; 795 | | | s := s + 1; 796 | | | s := s + 1; 797 | | | s := s + 1; 798 | | | s := s + 1; 799 | | | s := s + 1; 800 | | | s := s + 1; 801 | | | s := s + 1; 802 | | | s := s + 1; 803 | | | s := s + 1; 804 | | | s := s + 1; 805 | | | s := s + 1; 806 | | | s := s + 1; 807 | | | s := s + 1; 808 | | | s := s + 1; 809 | | | s := s + 1; 810 | | | s := s + 1; 811 | | | s := s + 1; 812 | | | s := s + 1; 813 | | | s := s + 1; 814 | | | s := s + 1; 815 | | | s := s + 1; 816 | | | s := s + 1; 817 | | | s := s + 1; 818 | | | s := s + 1; 819 | | | s := s + 1; 820 | | | s := s + 1; 821 | | | s := s + 1; 822 | | | s := s + 1; 823 | | | s := s + 1; 824 | | | s := s + 1; 825 | | | s := s + 1; 826 | | | s := s + 1; 827 | | | s := s + 1; 828 | | | s := s + 1; 829 | | | s := s + 1; 830 | | | s := s + 1; 831 | | | s := s + 1; 832 | | | s := s + 1; 833 | | | s := s + 1; 834 | | | s := s + 1; 835 | | | s := s + 1; 836 | | | s := s + 1; 837 | | | s := s + 1; 838 | | | s := s + 1; 839 | | | s := s + 1; 840 | | | s := s + 1; 841 | | | s := s + 1; 842 | | | s := s + 1; 843 | | | s := s + 1; 844 | | | s := s + 1; 845 | | | s := s + 1; 846 | | | s := s + 1; 847 | | | s := s + 1; 848 | | | s := s + 1; 849 | | | s := s + 1; 850 | | | s := s + 1; 851 | | | s := s + 1; 852 | | | s := s + 1; 853 | | | s := s + 1; 854 | | | s := s + 1; 855 | | | s := s + 1; 856 | | | s := s + 1; 857 | | | s := s + 1; 858 | | | s := s + 1; 859 | | | s := s + 1; 860 | | | s := s + 1; 861 | | | s := s + 1; 862 | | | s := s + 1; 863 | | | s := s + 1; 864 | | | s := s + 1; 865 | | | s := s + 1; 866 | | | s := s + 1; 867 | | | s := s + 1; 868 | | | s := s + 1; 869 | | | s := s + 1; 870 | | | s := s + 1; 871 | | | s := s + 1; 872 | | | s := s + 1; 873 | | | s := s + 1; 874 | | | s := s + 1; 875 | | | s := s + 1; 876 | | | s := s + 1; 877 | | | s := s + 1; 878 | | | s := s + 1; 879 | | | s := s + 1; 880 | | | s := s + 1; 881 | | | s := s + 1; 882 | | | s := s + 1; 883 | | | s := s + 1; 884 | | | s := s + 1; 885 | | | s := s + 1; 886 | | | s := s + 1; 887 | | | s := s + 1; 888 | | | s := s + 1; 889 | | | s := s + 1; 890 | | | s := s + 1; 891 | | | s := s + 1; 892 | | | s := s + 1; 893 | | | s := s + 1; 894 | | | s := s + 1; 895 | | | s := s + 1; 896 | | | s := s + 1; 897 | | | s := s + 1; 898 | | | s := s + 1; 899 | | | s := s + 1; 900 | | | s := s + 1; 901 | | | s := s + 1; 902 | | | s := s + 1; 903 | | | s := s + 1; 904 | | | s := s + 1; 905 | | | s := s + 1; 906 | | | s := s + 1; 907 | | | s := s + 1; 908 | | | s := s + 1; 909 | | | s := s + 1; 910 | | | s := s + 1; 911 | | | s := s + 1; 912 | | | s := s + 1; 913 | | | s := s + 1; 914 | | | s := s + 1; 915 | | | s := s + 1; 916 | | | s := s + 1; 917 | | | s := s + 1; 918 | | | s := s + 1; 919 | | | s := s + 1; 920 | | | s := s + 1; 921 | | | s := s + 1; 922 | | | s := s + 1; 923 | | | s := s + 1; 924 | | | s := s + 1; 925 | | | s := s + 1; 926 | | | s := s + 1; 927 | | | s := s + 1; 928 | | | s := s + 1; 929 | | | s := s + 1; 930 | | | s := s + 1; 931 | | | s := s + 1; 932 | | | s := s + 1; 933 | | | s := s + 1; 934 | | | s := s + 1; 935 | | | s := s + 1; 936 | | | s := s + 1; 937 | | | s := s + 1; 938 | | | s := s + 1; 939 | | | s := s + 1; 940 | | | s := s + 1; 941 | | | s := s + 1; 942 | | | s := s + 1; 943 | | | s := s + 1; 944 | | | s := s + 1; 945 | | | s := s + 1; 946 | | | s := s + 1; 947 | | | s := s + 1; 948 | | | s := s + 1; 949 | | | s := s + 1; 950 | | | s := s + 1; 951 | | | s := s + 1; 952 | | | s := s + 1; 953 | | | s := s + 1; 954 | | | s := s + 1; 955 | | | s := s + 1; 956 | | | s := s + 1; 957 | | | s := s + 1; 958 | | | s := s + 1; 959 | | | s := s + 1; 960 | | | s := s + 1; 961 | | | s := s + 1; 962 | | | s := s + 1; 963 | | | s := s + 1; 964 | | | s := s + 1; 965 | | | s := s + 1; 966 | | | s := s + 1; 967 | | | s := s + 1; 968 | | | s := s + 1; 969 | | | s := s + 1; 970 | | | s := s + 1; 971 | | | s := s + 1; 972 | | | s := s + 1; 973 | | | s := s + 1; 974 | | | s := s + 1; 975 | | | s := s + 1; 976 | | | s := s + 1; 977 | | | s := s + 1; 978 | | | s := s + 1; 979 | | | s := s + 1; 980 | | | s := s + 1; 981 | | | s := s + 1; 982 | | | s := s + 1; 983 | | | s := s + 1; 984 | | | s := s + 1; 985 | | | s := s + 1; 986 | | | s := s + 1; 987 | | | s := s + 1; 988 | | | s := s + 1; 989 | | | s := s + 1; 990 | | | s := s + 1; 991 | | | s := s + 1; 992 | | | s := s + 1; 993 | | | s := s + 1; 994 | | | s := s + 1; 995 | | | s := s + 1; 996 | | | s := s + 1; 997 | | | s := s + 1; 998 | | | s := s + 1; 999 | | | s := s + 1; 1000 | | | s := s + 1; 1001 | | | s := s + 1; 1002 | | | s := s + 1; 1003 | | | s := s + 1; 1004 | | | s := s + 1; 1005 | | | s := s + 1; 1006 | | | s := s + 1; 1007 | | | s := s + 1; 1008 | | | s := s + 1; 1009 | | | s := s + 1; 1010 | | | s := s + 1; 1011 | | | s := s + 1; 1012 | | | s := s + 1; 1013 | | | s := s + 1; 1014 | | | s := s + 1; 1015 | | | s := s + 1; 1016 | | | s := s + 1; 1017 | | | s := s + 1; 1018 | | | s := s + 1; 1019 | | | s := s + 1; 1020 | | | s := s + 1; 1021 | | | s := s + 1; 1022 | | | s := s + 1; 1023 | | | s := s + 1; 1024 | | | s := s + 1; 1025 | | | s := s + 1; 1026 | | | s := s + 1; 1027 | | | s := s + 1; 1028 | | | s := s + 1; 1029 | | | s := s + 1; 1030 | | | s := s + 1; 1031 | | | s := s + 1; 1032 | | | s := s + 1; 1033 | | | s := s + 1; 1034 | | | s := s + 1; 1035 | | | s := s + 1; 1036 | | | s := s + 1; 1037 | | | s := s + 1; 1038 | | | end loop; 1039 | | | end loop; 1040 | | | j := j + 1; 1041 | | | end loop; 1042 | | | exception when others then 1043 | | | raise 'reraised exception %', sqlerrm; 1044 | | | end; 1045 | | | return $1; 1046 | | | end; (1046 rows) set plpgsql_check.profiler = on; select longfx(10); longfx -------- 10 (1 row) select longfx(10); longfx -------- 10 (1 row) set plpgsql_check.profiler = off; select longfx(10); longfx -------- 10 (1 row) select lineno, stmt_lineno, exec_stmts, source from plpgsql_profiler_function_tb('longfx'); lineno | stmt_lineno | exec_stmts | source --------+-------------+------------+----------------------------------------------------- 1 | | | 2 | | | declare 3 | | | s int default 0; 4 | | | j int default 0; 5 | | | r record; 6 | 6 | 2 | begin 7 | 7 | 2 | begin 8 | 8 | 2 | while j < 10 9 | | | loop 10 | 10 | 20 | for i in 1..1 11 | | | loop 12 | 12 | 20 | for r in select * from generate_series(1,1) 13 | | | loop 14 | 14 | 20 | s := s + 1; 15 | 15 | 20 | s := s + 1; 16 | 16 | 20 | s := s + 1; 17 | 17 | 20 | s := s + 1; 18 | 18 | 20 | s := s + 1; 19 | 19 | 20 | s := s + 1; 20 | 20 | 20 | s := s + 1; 21 | 21 | 20 | s := s + 1; 22 | 22 | 20 | s := s + 1; 23 | 23 | 20 | s := s + 1; 24 | 24 | 20 | s := s + 1; 25 | 25 | 20 | s := s + 1; 26 | 26 | 20 | s := s + 1; 27 | 27 | 20 | s := s + 1; 28 | 28 | 20 | s := s + 1; 29 | 29 | 20 | s := s + 1; 30 | 30 | 20 | s := s + 1; 31 | 31 | 20 | s := s + 1; 32 | 32 | 20 | s := s + 1; 33 | 33 | 20 | s := s + 1; 34 | 34 | 20 | s := s + 1; 35 | 35 | 20 | s := s + 1; 36 | 36 | 20 | s := s + 1; 37 | 37 | 20 | s := s + 1; 38 | 38 | 20 | s := s + 1; 39 | 39 | 20 | s := s + 1; 40 | 40 | 20 | s := s + 1; 41 | 41 | 20 | s := s + 1; 42 | 42 | 20 | s := s + 1; 43 | 43 | 20 | s := s + 1; 44 | 44 | 20 | s := s + 1; 45 | 45 | 20 | s := s + 1; 46 | 46 | 20 | s := s + 1; 47 | 47 | 20 | s := s + 1; 48 | 48 | 20 | s := s + 1; 49 | 49 | 20 | s := s + 1; 50 | 50 | 20 | s := s + 1; 51 | 51 | 20 | s := s + 1; 52 | 52 | 20 | s := s + 1; 53 | 53 | 20 | s := s + 1; 54 | 54 | 20 | s := s + 1; 55 | 55 | 20 | s := s + 1; 56 | 56 | 20 | s := s + 1; 57 | 57 | 20 | s := s + 1; 58 | 58 | 20 | s := s + 1; 59 | 59 | 20 | s := s + 1; 60 | 60 | 20 | s := s + 1; 61 | 61 | 20 | s := s + 1; 62 | 62 | 20 | s := s + 1; 63 | 63 | 20 | s := s + 1; 64 | 64 | 20 | s := s + 1; 65 | 65 | 20 | s := s + 1; 66 | 66 | 20 | s := s + 1; 67 | 67 | 20 | s := s + 1; 68 | 68 | 20 | s := s + 1; 69 | 69 | 20 | s := s + 1; 70 | 70 | 20 | s := s + 1; 71 | 71 | 20 | s := s + 1; 72 | 72 | 20 | s := s + 1; 73 | 73 | 20 | s := s + 1; 74 | 74 | 20 | s := s + 1; 75 | 75 | 20 | s := s + 1; 76 | 76 | 20 | s := s + 1; 77 | 77 | 20 | s := s + 1; 78 | 78 | 20 | s := s + 1; 79 | 79 | 20 | s := s + 1; 80 | 80 | 20 | s := s + 1; 81 | 81 | 20 | s := s + 1; 82 | 82 | 20 | s := s + 1; 83 | 83 | 20 | s := s + 1; 84 | 84 | 20 | s := s + 1; 85 | 85 | 20 | s := s + 1; 86 | 86 | 20 | s := s + 1; 87 | 87 | 20 | s := s + 1; 88 | 88 | 20 | s := s + 1; 89 | 89 | 20 | s := s + 1; 90 | 90 | 20 | s := s + 1; 91 | 91 | 20 | s := s + 1; 92 | 92 | 20 | s := s + 1; 93 | 93 | 20 | s := s + 1; 94 | 94 | 20 | s := s + 1; 95 | 95 | 20 | s := s + 1; 96 | 96 | 20 | s := s + 1; 97 | 97 | 20 | s := s + 1; 98 | 98 | 20 | s := s + 1; 99 | 99 | 20 | s := s + 1; 100 | 100 | 20 | s := s + 1; 101 | 101 | 20 | s := s + 1; 102 | 102 | 20 | s := s + 1; 103 | 103 | 20 | s := s + 1; 104 | 104 | 20 | s := s + 1; 105 | 105 | 20 | s := s + 1; 106 | 106 | 20 | s := s + 1; 107 | 107 | 20 | s := s + 1; 108 | 108 | 20 | s := s + 1; 109 | 109 | 20 | s := s + 1; 110 | 110 | 20 | s := s + 1; 111 | 111 | 20 | s := s + 1; 112 | 112 | 20 | s := s + 1; 113 | 113 | 20 | s := s + 1; 114 | 114 | 20 | s := s + 1; 115 | 115 | 20 | s := s + 1; 116 | 116 | 20 | s := s + 1; 117 | 117 | 20 | s := s + 1; 118 | 118 | 20 | s := s + 1; 119 | 119 | 20 | s := s + 1; 120 | 120 | 20 | s := s + 1; 121 | 121 | 20 | s := s + 1; 122 | 122 | 20 | s := s + 1; 123 | 123 | 20 | s := s + 1; 124 | 124 | 20 | s := s + 1; 125 | 125 | 20 | s := s + 1; 126 | 126 | 20 | s := s + 1; 127 | 127 | 20 | s := s + 1; 128 | 128 | 20 | s := s + 1; 129 | 129 | 20 | s := s + 1; 130 | 130 | 20 | s := s + 1; 131 | 131 | 20 | s := s + 1; 132 | 132 | 20 | s := s + 1; 133 | 133 | 20 | s := s + 1; 134 | 134 | 20 | s := s + 1; 135 | 135 | 20 | s := s + 1; 136 | 136 | 20 | s := s + 1; 137 | 137 | 20 | s := s + 1; 138 | 138 | 20 | s := s + 1; 139 | 139 | 20 | s := s + 1; 140 | 140 | 20 | s := s + 1; 141 | 141 | 20 | s := s + 1; 142 | 142 | 20 | s := s + 1; 143 | 143 | 20 | s := s + 1; 144 | 144 | 20 | s := s + 1; 145 | 145 | 20 | s := s + 1; 146 | 146 | 20 | s := s + 1; 147 | 147 | 20 | s := s + 1; 148 | 148 | 20 | s := s + 1; 149 | 149 | 20 | s := s + 1; 150 | 150 | 20 | s := s + 1; 151 | 151 | 20 | s := s + 1; 152 | 152 | 20 | s := s + 1; 153 | 153 | 20 | s := s + 1; 154 | 154 | 20 | s := s + 1; 155 | 155 | 20 | s := s + 1; 156 | 156 | 20 | s := s + 1; 157 | 157 | 20 | s := s + 1; 158 | 158 | 20 | s := s + 1; 159 | 159 | 20 | s := s + 1; 160 | 160 | 20 | s := s + 1; 161 | 161 | 20 | s := s + 1; 162 | 162 | 20 | s := s + 1; 163 | 163 | 20 | s := s + 1; 164 | 164 | 20 | s := s + 1; 165 | 165 | 20 | s := s + 1; 166 | 166 | 20 | s := s + 1; 167 | 167 | 20 | s := s + 1; 168 | 168 | 20 | s := s + 1; 169 | 169 | 20 | s := s + 1; 170 | 170 | 20 | s := s + 1; 171 | 171 | 20 | s := s + 1; 172 | 172 | 20 | s := s + 1; 173 | 173 | 20 | s := s + 1; 174 | 174 | 20 | s := s + 1; 175 | 175 | 20 | s := s + 1; 176 | 176 | 20 | s := s + 1; 177 | 177 | 20 | s := s + 1; 178 | 178 | 20 | s := s + 1; 179 | 179 | 20 | s := s + 1; 180 | 180 | 20 | s := s + 1; 181 | 181 | 20 | s := s + 1; 182 | 182 | 20 | s := s + 1; 183 | 183 | 20 | s := s + 1; 184 | 184 | 20 | s := s + 1; 185 | 185 | 20 | s := s + 1; 186 | 186 | 20 | s := s + 1; 187 | 187 | 20 | s := s + 1; 188 | 188 | 20 | s := s + 1; 189 | 189 | 20 | s := s + 1; 190 | 190 | 20 | s := s + 1; 191 | 191 | 20 | s := s + 1; 192 | 192 | 20 | s := s + 1; 193 | 193 | 20 | s := s + 1; 194 | 194 | 20 | s := s + 1; 195 | 195 | 20 | s := s + 1; 196 | 196 | 20 | s := s + 1; 197 | 197 | 20 | s := s + 1; 198 | 198 | 20 | s := s + 1; 199 | 199 | 20 | s := s + 1; 200 | 200 | 20 | s := s + 1; 201 | 201 | 20 | s := s + 1; 202 | 202 | 20 | s := s + 1; 203 | 203 | 20 | s := s + 1; 204 | 204 | 20 | s := s + 1; 205 | 205 | 20 | s := s + 1; 206 | 206 | 20 | s := s + 1; 207 | 207 | 20 | s := s + 1; 208 | 208 | 20 | s := s + 1; 209 | 209 | 20 | s := s + 1; 210 | 210 | 20 | s := s + 1; 211 | 211 | 20 | s := s + 1; 212 | 212 | 20 | s := s + 1; 213 | 213 | 20 | s := s + 1; 214 | 214 | 20 | s := s + 1; 215 | 215 | 20 | s := s + 1; 216 | 216 | 20 | s := s + 1; 217 | 217 | 20 | s := s + 1; 218 | 218 | 20 | s := s + 1; 219 | 219 | 20 | s := s + 1; 220 | 220 | 20 | s := s + 1; 221 | 221 | 20 | s := s + 1; 222 | 222 | 20 | s := s + 1; 223 | 223 | 20 | s := s + 1; 224 | 224 | 20 | s := s + 1; 225 | 225 | 20 | s := s + 1; 226 | 226 | 20 | s := s + 1; 227 | 227 | 20 | s := s + 1; 228 | 228 | 20 | s := s + 1; 229 | 229 | 20 | s := s + 1; 230 | 230 | 20 | s := s + 1; 231 | 231 | 20 | s := s + 1; 232 | 232 | 20 | s := s + 1; 233 | 233 | 20 | s := s + 1; 234 | 234 | 20 | s := s + 1; 235 | 235 | 20 | s := s + 1; 236 | 236 | 20 | s := s + 1; 237 | 237 | 20 | s := s + 1; 238 | 238 | 20 | s := s + 1; 239 | 239 | 20 | s := s + 1; 240 | 240 | 20 | s := s + 1; 241 | 241 | 20 | s := s + 1; 242 | 242 | 20 | s := s + 1; 243 | 243 | 20 | s := s + 1; 244 | 244 | 20 | s := s + 1; 245 | 245 | 20 | s := s + 1; 246 | 246 | 20 | s := s + 1; 247 | 247 | 20 | s := s + 1; 248 | 248 | 20 | s := s + 1; 249 | 249 | 20 | s := s + 1; 250 | 250 | 20 | s := s + 1; 251 | 251 | 20 | s := s + 1; 252 | 252 | 20 | s := s + 1; 253 | 253 | 20 | s := s + 1; 254 | 254 | 20 | s := s + 1; 255 | 255 | 20 | s := s + 1; 256 | 256 | 20 | s := s + 1; 257 | 257 | 20 | s := s + 1; 258 | 258 | 20 | s := s + 1; 259 | 259 | 20 | s := s + 1; 260 | 260 | 20 | s := s + 1; 261 | 261 | 20 | s := s + 1; 262 | 262 | 20 | s := s + 1; 263 | 263 | 20 | s := s + 1; 264 | 264 | 20 | s := s + 1; 265 | 265 | 20 | s := s + 1; 266 | 266 | 20 | s := s + 1; 267 | 267 | 20 | s := s + 1; 268 | 268 | 20 | s := s + 1; 269 | 269 | 20 | s := s + 1; 270 | 270 | 20 | s := s + 1; 271 | 271 | 20 | s := s + 1; 272 | 272 | 20 | s := s + 1; 273 | 273 | 20 | s := s + 1; 274 | 274 | 20 | s := s + 1; 275 | 275 | 20 | s := s + 1; 276 | 276 | 20 | s := s + 1; 277 | 277 | 20 | s := s + 1; 278 | 278 | 20 | s := s + 1; 279 | 279 | 20 | s := s + 1; 280 | 280 | 20 | s := s + 1; 281 | 281 | 20 | s := s + 1; 282 | 282 | 20 | s := s + 1; 283 | 283 | 20 | s := s + 1; 284 | 284 | 20 | s := s + 1; 285 | 285 | 20 | s := s + 1; 286 | 286 | 20 | s := s + 1; 287 | 287 | 20 | s := s + 1; 288 | 288 | 20 | s := s + 1; 289 | 289 | 20 | s := s + 1; 290 | 290 | 20 | s := s + 1; 291 | 291 | 20 | s := s + 1; 292 | 292 | 20 | s := s + 1; 293 | 293 | 20 | s := s + 1; 294 | 294 | 20 | s := s + 1; 295 | 295 | 20 | s := s + 1; 296 | 296 | 20 | s := s + 1; 297 | 297 | 20 | s := s + 1; 298 | 298 | 20 | s := s + 1; 299 | 299 | 20 | s := s + 1; 300 | 300 | 20 | s := s + 1; 301 | 301 | 20 | s := s + 1; 302 | 302 | 20 | s := s + 1; 303 | 303 | 20 | s := s + 1; 304 | 304 | 20 | s := s + 1; 305 | 305 | 20 | s := s + 1; 306 | 306 | 20 | s := s + 1; 307 | 307 | 20 | s := s + 1; 308 | 308 | 20 | s := s + 1; 309 | 309 | 20 | s := s + 1; 310 | 310 | 20 | s := s + 1; 311 | 311 | 20 | s := s + 1; 312 | 312 | 20 | s := s + 1; 313 | 313 | 20 | s := s + 1; 314 | 314 | 20 | s := s + 1; 315 | 315 | 20 | s := s + 1; 316 | 316 | 20 | s := s + 1; 317 | 317 | 20 | s := s + 1; 318 | 318 | 20 | s := s + 1; 319 | 319 | 20 | s := s + 1; 320 | 320 | 20 | s := s + 1; 321 | 321 | 20 | s := s + 1; 322 | 322 | 20 | s := s + 1; 323 | 323 | 20 | s := s + 1; 324 | 324 | 20 | s := s + 1; 325 | 325 | 20 | s := s + 1; 326 | 326 | 20 | s := s + 1; 327 | 327 | 20 | s := s + 1; 328 | 328 | 20 | s := s + 1; 329 | 329 | 20 | s := s + 1; 330 | 330 | 20 | s := s + 1; 331 | 331 | 20 | s := s + 1; 332 | 332 | 20 | s := s + 1; 333 | 333 | 20 | s := s + 1; 334 | 334 | 20 | s := s + 1; 335 | 335 | 20 | s := s + 1; 336 | 336 | 20 | s := s + 1; 337 | 337 | 20 | s := s + 1; 338 | 338 | 20 | s := s + 1; 339 | 339 | 20 | s := s + 1; 340 | 340 | 20 | s := s + 1; 341 | 341 | 20 | s := s + 1; 342 | 342 | 20 | s := s + 1; 343 | 343 | 20 | s := s + 1; 344 | 344 | 20 | s := s + 1; 345 | 345 | 20 | s := s + 1; 346 | 346 | 20 | s := s + 1; 347 | 347 | 20 | s := s + 1; 348 | 348 | 20 | s := s + 1; 349 | 349 | 20 | s := s + 1; 350 | 350 | 20 | s := s + 1; 351 | 351 | 20 | s := s + 1; 352 | 352 | 20 | s := s + 1; 353 | 353 | 20 | s := s + 1; 354 | 354 | 20 | s := s + 1; 355 | 355 | 20 | s := s + 1; 356 | 356 | 20 | s := s + 1; 357 | 357 | 20 | s := s + 1; 358 | 358 | 20 | s := s + 1; 359 | 359 | 20 | s := s + 1; 360 | 360 | 20 | s := s + 1; 361 | 361 | 20 | s := s + 1; 362 | 362 | 20 | s := s + 1; 363 | 363 | 20 | s := s + 1; 364 | 364 | 20 | s := s + 1; 365 | 365 | 20 | s := s + 1; 366 | 366 | 20 | s := s + 1; 367 | 367 | 20 | s := s + 1; 368 | 368 | 20 | s := s + 1; 369 | 369 | 20 | s := s + 1; 370 | 370 | 20 | s := s + 1; 371 | 371 | 20 | s := s + 1; 372 | 372 | 20 | s := s + 1; 373 | 373 | 20 | s := s + 1; 374 | 374 | 20 | s := s + 1; 375 | 375 | 20 | s := s + 1; 376 | 376 | 20 | s := s + 1; 377 | 377 | 20 | s := s + 1; 378 | 378 | 20 | s := s + 1; 379 | 379 | 20 | s := s + 1; 380 | 380 | 20 | s := s + 1; 381 | 381 | 20 | s := s + 1; 382 | 382 | 20 | s := s + 1; 383 | 383 | 20 | s := s + 1; 384 | 384 | 20 | s := s + 1; 385 | 385 | 20 | s := s + 1; 386 | 386 | 20 | s := s + 1; 387 | 387 | 20 | s := s + 1; 388 | 388 | 20 | s := s + 1; 389 | 389 | 20 | s := s + 1; 390 | 390 | 20 | s := s + 1; 391 | 391 | 20 | s := s + 1; 392 | 392 | 20 | s := s + 1; 393 | 393 | 20 | s := s + 1; 394 | 394 | 20 | s := s + 1; 395 | 395 | 20 | s := s + 1; 396 | 396 | 20 | s := s + 1; 397 | 397 | 20 | s := s + 1; 398 | 398 | 20 | s := s + 1; 399 | 399 | 20 | s := s + 1; 400 | 400 | 20 | s := s + 1; 401 | 401 | 20 | s := s + 1; 402 | 402 | 20 | s := s + 1; 403 | 403 | 20 | s := s + 1; 404 | 404 | 20 | s := s + 1; 405 | 405 | 20 | s := s + 1; 406 | 406 | 20 | s := s + 1; 407 | 407 | 20 | s := s + 1; 408 | 408 | 20 | s := s + 1; 409 | 409 | 20 | s := s + 1; 410 | 410 | 20 | s := s + 1; 411 | 411 | 20 | s := s + 1; 412 | 412 | 20 | s := s + 1; 413 | 413 | 20 | s := s + 1; 414 | 414 | 20 | s := s + 1; 415 | 415 | 20 | s := s + 1; 416 | 416 | 20 | s := s + 1; 417 | 417 | 20 | s := s + 1; 418 | 418 | 20 | s := s + 1; 419 | 419 | 20 | s := s + 1; 420 | 420 | 20 | s := s + 1; 421 | 421 | 20 | s := s + 1; 422 | 422 | 20 | s := s + 1; 423 | 423 | 20 | s := s + 1; 424 | 424 | 20 | s := s + 1; 425 | 425 | 20 | s := s + 1; 426 | 426 | 20 | s := s + 1; 427 | 427 | 20 | s := s + 1; 428 | 428 | 20 | s := s + 1; 429 | 429 | 20 | s := s + 1; 430 | 430 | 20 | s := s + 1; 431 | 431 | 20 | s := s + 1; 432 | 432 | 20 | s := s + 1; 433 | 433 | 20 | s := s + 1; 434 | 434 | 20 | s := s + 1; 435 | 435 | 20 | s := s + 1; 436 | 436 | 20 | s := s + 1; 437 | 437 | 20 | s := s + 1; 438 | 438 | 20 | s := s + 1; 439 | 439 | 20 | s := s + 1; 440 | 440 | 20 | s := s + 1; 441 | 441 | 20 | s := s + 1; 442 | 442 | 20 | s := s + 1; 443 | 443 | 20 | s := s + 1; 444 | 444 | 20 | s := s + 1; 445 | 445 | 20 | s := s + 1; 446 | 446 | 20 | s := s + 1; 447 | 447 | 20 | s := s + 1; 448 | 448 | 20 | s := s + 1; 449 | 449 | 20 | s := s + 1; 450 | 450 | 20 | s := s + 1; 451 | 451 | 20 | s := s + 1; 452 | 452 | 20 | s := s + 1; 453 | 453 | 20 | s := s + 1; 454 | 454 | 20 | s := s + 1; 455 | 455 | 20 | s := s + 1; 456 | 456 | 20 | s := s + 1; 457 | 457 | 20 | s := s + 1; 458 | 458 | 20 | s := s + 1; 459 | 459 | 20 | s := s + 1; 460 | 460 | 20 | s := s + 1; 461 | 461 | 20 | s := s + 1; 462 | 462 | 20 | s := s + 1; 463 | 463 | 20 | s := s + 1; 464 | 464 | 20 | s := s + 1; 465 | 465 | 20 | s := s + 1; 466 | 466 | 20 | s := s + 1; 467 | 467 | 20 | s := s + 1; 468 | 468 | 20 | s := s + 1; 469 | 469 | 20 | s := s + 1; 470 | 470 | 20 | s := s + 1; 471 | 471 | 20 | s := s + 1; 472 | 472 | 20 | s := s + 1; 473 | 473 | 20 | s := s + 1; 474 | 474 | 20 | s := s + 1; 475 | 475 | 20 | s := s + 1; 476 | 476 | 20 | s := s + 1; 477 | 477 | 20 | s := s + 1; 478 | 478 | 20 | s := s + 1; 479 | 479 | 20 | s := s + 1; 480 | 480 | 20 | s := s + 1; 481 | 481 | 20 | s := s + 1; 482 | 482 | 20 | s := s + 1; 483 | 483 | 20 | s := s + 1; 484 | 484 | 20 | s := s + 1; 485 | 485 | 20 | s := s + 1; 486 | 486 | 20 | s := s + 1; 487 | 487 | 20 | s := s + 1; 488 | 488 | 20 | s := s + 1; 489 | 489 | 20 | s := s + 1; 490 | 490 | 20 | s := s + 1; 491 | 491 | 20 | s := s + 1; 492 | 492 | 20 | s := s + 1; 493 | 493 | 20 | s := s + 1; 494 | 494 | 20 | s := s + 1; 495 | 495 | 20 | s := s + 1; 496 | 496 | 20 | s := s + 1; 497 | 497 | 20 | s := s + 1; 498 | 498 | 20 | s := s + 1; 499 | 499 | 20 | s := s + 1; 500 | 500 | 20 | s := s + 1; 501 | 501 | 20 | s := s + 1; 502 | 502 | 20 | s := s + 1; 503 | 503 | 20 | s := s + 1; 504 | 504 | 20 | s := s + 1; 505 | 505 | 20 | s := s + 1; 506 | 506 | 20 | s := s + 1; 507 | 507 | 20 | s := s + 1; 508 | 508 | 20 | s := s + 1; 509 | 509 | 20 | s := s + 1; 510 | 510 | 20 | s := s + 1; 511 | 511 | 20 | s := s + 1; 512 | 512 | 20 | s := s + 1; 513 | 513 | 20 | s := s + 1; 514 | 514 | 20 | s := s + 1; 515 | 515 | 20 | s := s + 1; 516 | 516 | 20 | s := s + 1; 517 | 517 | 20 | s := s + 1; 518 | 518 | 20 | s := s + 1; 519 | 519 | 20 | s := s + 1; 520 | 520 | 20 | s := s + 1; 521 | 521 | 20 | s := s + 1; 522 | 522 | 20 | s := s + 1; 523 | 523 | 20 | s := s + 1; 524 | 524 | 20 | s := s + 1; 525 | 525 | 20 | s := s + 1; 526 | 526 | 20 | s := s + 1; 527 | 527 | 20 | s := s + 1; 528 | 528 | 20 | s := s + 1; 529 | 529 | 20 | s := s + 1; 530 | 530 | 20 | s := s + 1; 531 | 531 | 20 | s := s + 1; 532 | 532 | 20 | s := s + 1; 533 | 533 | 20 | s := s + 1; 534 | 534 | 20 | s := s + 1; 535 | 535 | 20 | s := s + 1; 536 | 536 | 20 | s := s + 1; 537 | 537 | 20 | s := s + 1; 538 | 538 | 20 | s := s + 1; 539 | 539 | 20 | s := s + 1; 540 | 540 | 20 | s := s + 1; 541 | 541 | 20 | s := s + 1; 542 | 542 | 20 | s := s + 1; 543 | 543 | 20 | s := s + 1; 544 | 544 | 20 | s := s + 1; 545 | 545 | 20 | s := s + 1; 546 | 546 | 20 | s := s + 1; 547 | 547 | 20 | s := s + 1; 548 | 548 | 20 | s := s + 1; 549 | 549 | 20 | s := s + 1; 550 | 550 | 20 | s := s + 1; 551 | 551 | 20 | s := s + 1; 552 | 552 | 20 | s := s + 1; 553 | 553 | 20 | s := s + 1; 554 | 554 | 20 | s := s + 1; 555 | 555 | 20 | s := s + 1; 556 | 556 | 20 | s := s + 1; 557 | 557 | 20 | s := s + 1; 558 | 558 | 20 | s := s + 1; 559 | 559 | 20 | s := s + 1; 560 | 560 | 20 | s := s + 1; 561 | 561 | 20 | s := s + 1; 562 | 562 | 20 | s := s + 1; 563 | 563 | 20 | s := s + 1; 564 | 564 | 20 | s := s + 1; 565 | 565 | 20 | s := s + 1; 566 | 566 | 20 | s := s + 1; 567 | 567 | 20 | s := s + 1; 568 | 568 | 20 | s := s + 1; 569 | 569 | 20 | s := s + 1; 570 | 570 | 20 | s := s + 1; 571 | 571 | 20 | s := s + 1; 572 | 572 | 20 | s := s + 1; 573 | 573 | 20 | s := s + 1; 574 | 574 | 20 | s := s + 1; 575 | 575 | 20 | s := s + 1; 576 | 576 | 20 | s := s + 1; 577 | 577 | 20 | s := s + 1; 578 | 578 | 20 | s := s + 1; 579 | 579 | 20 | s := s + 1; 580 | 580 | 20 | s := s + 1; 581 | 581 | 20 | s := s + 1; 582 | 582 | 20 | s := s + 1; 583 | 583 | 20 | s := s + 1; 584 | 584 | 20 | s := s + 1; 585 | 585 | 20 | s := s + 1; 586 | 586 | 20 | s := s + 1; 587 | 587 | 20 | s := s + 1; 588 | 588 | 20 | s := s + 1; 589 | 589 | 20 | s := s + 1; 590 | 590 | 20 | s := s + 1; 591 | 591 | 20 | s := s + 1; 592 | 592 | 20 | s := s + 1; 593 | 593 | 20 | s := s + 1; 594 | 594 | 20 | s := s + 1; 595 | 595 | 20 | s := s + 1; 596 | 596 | 20 | s := s + 1; 597 | 597 | 20 | s := s + 1; 598 | 598 | 20 | s := s + 1; 599 | 599 | 20 | s := s + 1; 600 | 600 | 20 | s := s + 1; 601 | 601 | 20 | s := s + 1; 602 | 602 | 20 | s := s + 1; 603 | 603 | 20 | s := s + 1; 604 | 604 | 20 | s := s + 1; 605 | 605 | 20 | s := s + 1; 606 | 606 | 20 | s := s + 1; 607 | 607 | 20 | s := s + 1; 608 | 608 | 20 | s := s + 1; 609 | 609 | 20 | s := s + 1; 610 | 610 | 20 | s := s + 1; 611 | 611 | 20 | s := s + 1; 612 | 612 | 20 | s := s + 1; 613 | 613 | 20 | s := s + 1; 614 | 614 | 20 | s := s + 1; 615 | 615 | 20 | s := s + 1; 616 | 616 | 20 | s := s + 1; 617 | 617 | 20 | s := s + 1; 618 | 618 | 20 | s := s + 1; 619 | 619 | 20 | s := s + 1; 620 | 620 | 20 | s := s + 1; 621 | 621 | 20 | s := s + 1; 622 | 622 | 20 | s := s + 1; 623 | 623 | 20 | s := s + 1; 624 | 624 | 20 | s := s + 1; 625 | 625 | 20 | s := s + 1; 626 | 626 | 20 | s := s + 1; 627 | 627 | 20 | s := s + 1; 628 | 628 | 20 | s := s + 1; 629 | 629 | 20 | s := s + 1; 630 | 630 | 20 | s := s + 1; 631 | 631 | 20 | s := s + 1; 632 | 632 | 20 | s := s + 1; 633 | 633 | 20 | s := s + 1; 634 | 634 | 20 | s := s + 1; 635 | 635 | 20 | s := s + 1; 636 | 636 | 20 | s := s + 1; 637 | 637 | 20 | s := s + 1; 638 | 638 | 20 | s := s + 1; 639 | 639 | 20 | s := s + 1; 640 | 640 | 20 | s := s + 1; 641 | 641 | 20 | s := s + 1; 642 | 642 | 20 | s := s + 1; 643 | 643 | 20 | s := s + 1; 644 | 644 | 20 | s := s + 1; 645 | 645 | 20 | s := s + 1; 646 | 646 | 20 | s := s + 1; 647 | 647 | 20 | s := s + 1; 648 | 648 | 20 | s := s + 1; 649 | 649 | 20 | s := s + 1; 650 | 650 | 20 | s := s + 1; 651 | 651 | 20 | s := s + 1; 652 | 652 | 20 | s := s + 1; 653 | 653 | 20 | s := s + 1; 654 | 654 | 20 | s := s + 1; 655 | 655 | 20 | s := s + 1; 656 | 656 | 20 | s := s + 1; 657 | 657 | 20 | s := s + 1; 658 | 658 | 20 | s := s + 1; 659 | 659 | 20 | s := s + 1; 660 | 660 | 20 | s := s + 1; 661 | 661 | 20 | s := s + 1; 662 | 662 | 20 | s := s + 1; 663 | 663 | 20 | s := s + 1; 664 | 664 | 20 | s := s + 1; 665 | 665 | 20 | s := s + 1; 666 | 666 | 20 | s := s + 1; 667 | 667 | 20 | s := s + 1; 668 | 668 | 20 | s := s + 1; 669 | 669 | 20 | s := s + 1; 670 | 670 | 20 | s := s + 1; 671 | 671 | 20 | s := s + 1; 672 | 672 | 20 | s := s + 1; 673 | 673 | 20 | s := s + 1; 674 | 674 | 20 | s := s + 1; 675 | 675 | 20 | s := s + 1; 676 | 676 | 20 | s := s + 1; 677 | 677 | 20 | s := s + 1; 678 | 678 | 20 | s := s + 1; 679 | 679 | 20 | s := s + 1; 680 | 680 | 20 | s := s + 1; 681 | 681 | 20 | s := s + 1; 682 | 682 | 20 | s := s + 1; 683 | 683 | 20 | s := s + 1; 684 | 684 | 20 | s := s + 1; 685 | 685 | 20 | s := s + 1; 686 | 686 | 20 | s := s + 1; 687 | 687 | 20 | s := s + 1; 688 | 688 | 20 | s := s + 1; 689 | 689 | 20 | s := s + 1; 690 | 690 | 20 | s := s + 1; 691 | 691 | 20 | s := s + 1; 692 | 692 | 20 | s := s + 1; 693 | 693 | 20 | s := s + 1; 694 | 694 | 20 | s := s + 1; 695 | 695 | 20 | s := s + 1; 696 | 696 | 20 | s := s + 1; 697 | 697 | 20 | s := s + 1; 698 | 698 | 20 | s := s + 1; 699 | 699 | 20 | s := s + 1; 700 | 700 | 20 | s := s + 1; 701 | 701 | 20 | s := s + 1; 702 | 702 | 20 | s := s + 1; 703 | 703 | 20 | s := s + 1; 704 | 704 | 20 | s := s + 1; 705 | 705 | 20 | s := s + 1; 706 | 706 | 20 | s := s + 1; 707 | 707 | 20 | s := s + 1; 708 | 708 | 20 | s := s + 1; 709 | 709 | 20 | s := s + 1; 710 | 710 | 20 | s := s + 1; 711 | 711 | 20 | s := s + 1; 712 | 712 | 20 | s := s + 1; 713 | 713 | 20 | s := s + 1; 714 | 714 | 20 | s := s + 1; 715 | 715 | 20 | s := s + 1; 716 | 716 | 20 | s := s + 1; 717 | 717 | 20 | s := s + 1; 718 | 718 | 20 | s := s + 1; 719 | 719 | 20 | s := s + 1; 720 | 720 | 20 | s := s + 1; 721 | 721 | 20 | s := s + 1; 722 | 722 | 20 | s := s + 1; 723 | 723 | 20 | s := s + 1; 724 | 724 | 20 | s := s + 1; 725 | 725 | 20 | s := s + 1; 726 | 726 | 20 | s := s + 1; 727 | 727 | 20 | s := s + 1; 728 | 728 | 20 | s := s + 1; 729 | 729 | 20 | s := s + 1; 730 | 730 | 20 | s := s + 1; 731 | 731 | 20 | s := s + 1; 732 | 732 | 20 | s := s + 1; 733 | 733 | 20 | s := s + 1; 734 | 734 | 20 | s := s + 1; 735 | 735 | 20 | s := s + 1; 736 | 736 | 20 | s := s + 1; 737 | 737 | 20 | s := s + 1; 738 | 738 | 20 | s := s + 1; 739 | 739 | 20 | s := s + 1; 740 | 740 | 20 | s := s + 1; 741 | 741 | 20 | s := s + 1; 742 | 742 | 20 | s := s + 1; 743 | 743 | 20 | s := s + 1; 744 | 744 | 20 | s := s + 1; 745 | 745 | 20 | s := s + 1; 746 | 746 | 20 | s := s + 1; 747 | 747 | 20 | s := s + 1; 748 | 748 | 20 | s := s + 1; 749 | 749 | 20 | s := s + 1; 750 | 750 | 20 | s := s + 1; 751 | 751 | 20 | s := s + 1; 752 | 752 | 20 | s := s + 1; 753 | 753 | 20 | s := s + 1; 754 | 754 | 20 | s := s + 1; 755 | 755 | 20 | s := s + 1; 756 | 756 | 20 | s := s + 1; 757 | 757 | 20 | s := s + 1; 758 | 758 | 20 | s := s + 1; 759 | 759 | 20 | s := s + 1; 760 | 760 | 20 | s := s + 1; 761 | 761 | 20 | s := s + 1; 762 | 762 | 20 | s := s + 1; 763 | 763 | 20 | s := s + 1; 764 | 764 | 20 | s := s + 1; 765 | 765 | 20 | s := s + 1; 766 | 766 | 20 | s := s + 1; 767 | 767 | 20 | s := s + 1; 768 | 768 | 20 | s := s + 1; 769 | 769 | 20 | s := s + 1; 770 | 770 | 20 | s := s + 1; 771 | 771 | 20 | s := s + 1; 772 | 772 | 20 | s := s + 1; 773 | 773 | 20 | s := s + 1; 774 | 774 | 20 | s := s + 1; 775 | 775 | 20 | s := s + 1; 776 | 776 | 20 | s := s + 1; 777 | 777 | 20 | s := s + 1; 778 | 778 | 20 | s := s + 1; 779 | 779 | 20 | s := s + 1; 780 | 780 | 20 | s := s + 1; 781 | 781 | 20 | s := s + 1; 782 | 782 | 20 | s := s + 1; 783 | 783 | 20 | s := s + 1; 784 | 784 | 20 | s := s + 1; 785 | 785 | 20 | s := s + 1; 786 | 786 | 20 | s := s + 1; 787 | 787 | 20 | s := s + 1; 788 | 788 | 20 | s := s + 1; 789 | 789 | 20 | s := s + 1; 790 | 790 | 20 | s := s + 1; 791 | 791 | 20 | s := s + 1; 792 | 792 | 20 | s := s + 1; 793 | 793 | 20 | s := s + 1; 794 | 794 | 20 | s := s + 1; 795 | 795 | 20 | s := s + 1; 796 | 796 | 20 | s := s + 1; 797 | 797 | 20 | s := s + 1; 798 | 798 | 20 | s := s + 1; 799 | 799 | 20 | s := s + 1; 800 | 800 | 20 | s := s + 1; 801 | 801 | 20 | s := s + 1; 802 | 802 | 20 | s := s + 1; 803 | 803 | 20 | s := s + 1; 804 | 804 | 20 | s := s + 1; 805 | 805 | 20 | s := s + 1; 806 | 806 | 20 | s := s + 1; 807 | 807 | 20 | s := s + 1; 808 | 808 | 20 | s := s + 1; 809 | 809 | 20 | s := s + 1; 810 | 810 | 20 | s := s + 1; 811 | 811 | 20 | s := s + 1; 812 | 812 | 20 | s := s + 1; 813 | 813 | 20 | s := s + 1; 814 | 814 | 20 | s := s + 1; 815 | 815 | 20 | s := s + 1; 816 | 816 | 20 | s := s + 1; 817 | 817 | 20 | s := s + 1; 818 | 818 | 20 | s := s + 1; 819 | 819 | 20 | s := s + 1; 820 | 820 | 20 | s := s + 1; 821 | 821 | 20 | s := s + 1; 822 | 822 | 20 | s := s + 1; 823 | 823 | 20 | s := s + 1; 824 | 824 | 20 | s := s + 1; 825 | 825 | 20 | s := s + 1; 826 | 826 | 20 | s := s + 1; 827 | 827 | 20 | s := s + 1; 828 | 828 | 20 | s := s + 1; 829 | 829 | 20 | s := s + 1; 830 | 830 | 20 | s := s + 1; 831 | 831 | 20 | s := s + 1; 832 | 832 | 20 | s := s + 1; 833 | 833 | 20 | s := s + 1; 834 | 834 | 20 | s := s + 1; 835 | 835 | 20 | s := s + 1; 836 | 836 | 20 | s := s + 1; 837 | 837 | 20 | s := s + 1; 838 | 838 | 20 | s := s + 1; 839 | 839 | 20 | s := s + 1; 840 | 840 | 20 | s := s + 1; 841 | 841 | 20 | s := s + 1; 842 | 842 | 20 | s := s + 1; 843 | 843 | 20 | s := s + 1; 844 | 844 | 20 | s := s + 1; 845 | 845 | 20 | s := s + 1; 846 | 846 | 20 | s := s + 1; 847 | 847 | 20 | s := s + 1; 848 | 848 | 20 | s := s + 1; 849 | 849 | 20 | s := s + 1; 850 | 850 | 20 | s := s + 1; 851 | 851 | 20 | s := s + 1; 852 | 852 | 20 | s := s + 1; 853 | 853 | 20 | s := s + 1; 854 | 854 | 20 | s := s + 1; 855 | 855 | 20 | s := s + 1; 856 | 856 | 20 | s := s + 1; 857 | 857 | 20 | s := s + 1; 858 | 858 | 20 | s := s + 1; 859 | 859 | 20 | s := s + 1; 860 | 860 | 20 | s := s + 1; 861 | 861 | 20 | s := s + 1; 862 | 862 | 20 | s := s + 1; 863 | 863 | 20 | s := s + 1; 864 | 864 | 20 | s := s + 1; 865 | 865 | 20 | s := s + 1; 866 | 866 | 20 | s := s + 1; 867 | 867 | 20 | s := s + 1; 868 | 868 | 20 | s := s + 1; 869 | 869 | 20 | s := s + 1; 870 | 870 | 20 | s := s + 1; 871 | 871 | 20 | s := s + 1; 872 | 872 | 20 | s := s + 1; 873 | 873 | 20 | s := s + 1; 874 | 874 | 20 | s := s + 1; 875 | 875 | 20 | s := s + 1; 876 | 876 | 20 | s := s + 1; 877 | 877 | 20 | s := s + 1; 878 | 878 | 20 | s := s + 1; 879 | 879 | 20 | s := s + 1; 880 | 880 | 20 | s := s + 1; 881 | 881 | 20 | s := s + 1; 882 | 882 | 20 | s := s + 1; 883 | 883 | 20 | s := s + 1; 884 | 884 | 20 | s := s + 1; 885 | 885 | 20 | s := s + 1; 886 | 886 | 20 | s := s + 1; 887 | 887 | 20 | s := s + 1; 888 | 888 | 20 | s := s + 1; 889 | 889 | 20 | s := s + 1; 890 | 890 | 20 | s := s + 1; 891 | 891 | 20 | s := s + 1; 892 | 892 | 20 | s := s + 1; 893 | 893 | 20 | s := s + 1; 894 | 894 | 20 | s := s + 1; 895 | 895 | 20 | s := s + 1; 896 | 896 | 20 | s := s + 1; 897 | 897 | 20 | s := s + 1; 898 | 898 | 20 | s := s + 1; 899 | 899 | 20 | s := s + 1; 900 | 900 | 20 | s := s + 1; 901 | 901 | 20 | s := s + 1; 902 | 902 | 20 | s := s + 1; 903 | 903 | 20 | s := s + 1; 904 | 904 | 20 | s := s + 1; 905 | 905 | 20 | s := s + 1; 906 | 906 | 20 | s := s + 1; 907 | 907 | 20 | s := s + 1; 908 | 908 | 20 | s := s + 1; 909 | 909 | 20 | s := s + 1; 910 | 910 | 20 | s := s + 1; 911 | 911 | 20 | s := s + 1; 912 | 912 | 20 | s := s + 1; 913 | 913 | 20 | s := s + 1; 914 | 914 | 20 | s := s + 1; 915 | 915 | 20 | s := s + 1; 916 | 916 | 20 | s := s + 1; 917 | 917 | 20 | s := s + 1; 918 | 918 | 20 | s := s + 1; 919 | 919 | 20 | s := s + 1; 920 | 920 | 20 | s := s + 1; 921 | 921 | 20 | s := s + 1; 922 | 922 | 20 | s := s + 1; 923 | 923 | 20 | s := s + 1; 924 | 924 | 20 | s := s + 1; 925 | 925 | 20 | s := s + 1; 926 | 926 | 20 | s := s + 1; 927 | 927 | 20 | s := s + 1; 928 | 928 | 20 | s := s + 1; 929 | 929 | 20 | s := s + 1; 930 | 930 | 20 | s := s + 1; 931 | 931 | 20 | s := s + 1; 932 | 932 | 20 | s := s + 1; 933 | 933 | 20 | s := s + 1; 934 | 934 | 20 | s := s + 1; 935 | 935 | 20 | s := s + 1; 936 | 936 | 20 | s := s + 1; 937 | 937 | 20 | s := s + 1; 938 | 938 | 20 | s := s + 1; 939 | 939 | 20 | s := s + 1; 940 | 940 | 20 | s := s + 1; 941 | 941 | 20 | s := s + 1; 942 | 942 | 20 | s := s + 1; 943 | 943 | 20 | s := s + 1; 944 | 944 | 20 | s := s + 1; 945 | 945 | 20 | s := s + 1; 946 | 946 | 20 | s := s + 1; 947 | 947 | 20 | s := s + 1; 948 | 948 | 20 | s := s + 1; 949 | 949 | 20 | s := s + 1; 950 | 950 | 20 | s := s + 1; 951 | 951 | 20 | s := s + 1; 952 | 952 | 20 | s := s + 1; 953 | 953 | 20 | s := s + 1; 954 | 954 | 20 | s := s + 1; 955 | 955 | 20 | s := s + 1; 956 | 956 | 20 | s := s + 1; 957 | 957 | 20 | s := s + 1; 958 | 958 | 20 | s := s + 1; 959 | 959 | 20 | s := s + 1; 960 | 960 | 20 | s := s + 1; 961 | 961 | 20 | s := s + 1; 962 | 962 | 20 | s := s + 1; 963 | 963 | 20 | s := s + 1; 964 | 964 | 20 | s := s + 1; 965 | 965 | 20 | s := s + 1; 966 | 966 | 20 | s := s + 1; 967 | 967 | 20 | s := s + 1; 968 | 968 | 20 | s := s + 1; 969 | 969 | 20 | s := s + 1; 970 | 970 | 20 | s := s + 1; 971 | 971 | 20 | s := s + 1; 972 | 972 | 20 | s := s + 1; 973 | 973 | 20 | s := s + 1; 974 | 974 | 20 | s := s + 1; 975 | 975 | 20 | s := s + 1; 976 | 976 | 20 | s := s + 1; 977 | 977 | 20 | s := s + 1; 978 | 978 | 20 | s := s + 1; 979 | 979 | 20 | s := s + 1; 980 | 980 | 20 | s := s + 1; 981 | 981 | 20 | s := s + 1; 982 | 982 | 20 | s := s + 1; 983 | 983 | 20 | s := s + 1; 984 | 984 | 20 | s := s + 1; 985 | 985 | 20 | s := s + 1; 986 | 986 | 20 | s := s + 1; 987 | 987 | 20 | s := s + 1; 988 | 988 | 20 | s := s + 1; 989 | 989 | 20 | s := s + 1; 990 | 990 | 20 | s := s + 1; 991 | 991 | 20 | s := s + 1; 992 | 992 | 20 | s := s + 1; 993 | 993 | 20 | s := s + 1; 994 | 994 | 20 | s := s + 1; 995 | 995 | 20 | s := s + 1; 996 | 996 | 20 | s := s + 1; 997 | 997 | 20 | s := s + 1; 998 | 998 | 20 | s := s + 1; 999 | 999 | 20 | s := s + 1; 1000 | 1000 | 20 | s := s + 1; 1001 | 1001 | 20 | s := s + 1; 1002 | 1002 | 20 | s := s + 1; 1003 | 1003 | 20 | s := s + 1; 1004 | 1004 | 20 | s := s + 1; 1005 | 1005 | 20 | s := s + 1; 1006 | 1006 | 20 | s := s + 1; 1007 | 1007 | 20 | s := s + 1; 1008 | 1008 | 20 | s := s + 1; 1009 | 1009 | 20 | s := s + 1; 1010 | 1010 | 20 | s := s + 1; 1011 | 1011 | 20 | s := s + 1; 1012 | 1012 | 20 | s := s + 1; 1013 | 1013 | 20 | s := s + 1; 1014 | 1014 | 20 | s := s + 1; 1015 | 1015 | 20 | s := s + 1; 1016 | 1016 | 20 | s := s + 1; 1017 | 1017 | 20 | s := s + 1; 1018 | 1018 | 20 | s := s + 1; 1019 | 1019 | 20 | s := s + 1; 1020 | 1020 | 20 | s := s + 1; 1021 | 1021 | 20 | s := s + 1; 1022 | 1022 | 20 | s := s + 1; 1023 | 1023 | 20 | s := s + 1; 1024 | 1024 | 20 | s := s + 1; 1025 | 1025 | 20 | s := s + 1; 1026 | 1026 | 20 | s := s + 1; 1027 | 1027 | 20 | s := s + 1; 1028 | 1028 | 20 | s := s + 1; 1029 | 1029 | 20 | s := s + 1; 1030 | 1030 | 20 | s := s + 1; 1031 | 1031 | 20 | s := s + 1; 1032 | 1032 | 20 | s := s + 1; 1033 | 1033 | 20 | s := s + 1; 1034 | 1034 | 20 | s := s + 1; 1035 | 1035 | 20 | s := s + 1; 1036 | 1036 | 20 | s := s + 1; 1037 | 1037 | 20 | s := s + 1; 1038 | | | end loop; 1039 | | | end loop; 1040 | 1040 | 20 | j := j + 1; 1041 | | | end loop; 1042 | | | exception when others then 1043 | 1043 | 0 | raise 'reraised exception %', sqlerrm; 1044 | | | end; 1045 | 1045 | 2 | return $1; 1046 | | | end; (1046 rows) select funcoid, exec_count from plpgsql_profiler_functions_all(); funcoid | exec_count -----------------+------------ longfx(integer) | 2 (1 row) 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; -- coverage tests set plpgsql_check.profiler to on; create or replace function covtest(int) returns int as $$ declare a int = $1; begin a := a + 1; if a < 10 then a := a + 1; end if; a := a + 1; return a; end; $$ language plpgsql; set plpgsql_check.profiler to on; select covtest(10); covtest --------- 12 (1 row) select stmtid, exec_stmts, stmtname from plpgsql_profiler_function_statements_tb('covtest'); stmtid | exec_stmts | stmtname --------+------------+----------------- 1 | 1 | statement block 2 | 1 | assignment 3 | 1 | IF 4 | 0 | assignment 5 | 1 | assignment 6 | 1 | RETURN (6 rows) select plpgsql_coverage_statements('covtest'); plpgsql_coverage_statements ----------------------------- 0.8333333333333334 (1 row) select plpgsql_coverage_branches('covtest'); plpgsql_coverage_branches --------------------------- 0.5 (1 row) select covtest(1); covtest --------- 4 (1 row) select stmtid, exec_stmts, stmtname from plpgsql_profiler_function_statements_tb('covtest'); stmtid | exec_stmts | stmtname --------+------------+----------------- 1 | 2 | statement block 2 | 2 | assignment 3 | 2 | IF 4 | 1 | assignment 5 | 2 | assignment 6 | 2 | RETURN (6 rows) select plpgsql_coverage_statements('covtest'); plpgsql_coverage_statements ----------------------------- 1 (1 row) select plpgsql_coverage_branches('covtest'); plpgsql_coverage_branches --------------------------- 1 (1 row) set plpgsql_check.profiler to off; 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) NOTICE: #0 "a" => '10', "b" => '20', "c" => '08-15-2020', "d" => '3.14' NOTICE: #2 ->> start of function tracer_tab_trg_fx() (oid=0) 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) NOTICE: #0 "a" => '11', "b" => '21', "c" => '08-16-2020', "d" => '6.28' NOTICE: #2 ->> start of function tracer_tab_trg_fx() (oid=0) 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 | 8 | SELECT 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: SELECT 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: SELECT (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: SELECT exists(select * from foo) -- ^ error:42P01:4:ASSERT:relation "boo" does not exist Query: SELECT (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 warning:00000:5:SQL statement:too few attributes for composite variable error:42804:9:EXECUTE:cannot cast composite value of "typ2" type to a scalar value of "integer" type warning:00000:9:EXECUTE:too few attributes for composite variable warning extra:00000:2:DECLARE:never read variable "v" (5 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: SQL statement "SELECT 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: SQL statement "SELECT 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: SQL statement "SELECT 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 ""%I"" (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(); -- aborted function has profile too create or replace function test_function(a int) returns int as $$ begin if (a > 5) then a := a + 10; return a; else raise exception 'a < 5'; end if; end; $$ language plpgsql; set plpgsql_check.profiler to on; select test_function(1); ERROR: a < 5 select test_function(10); test_function --------------- 20 (1 row) select lineno, exec_stmts, exec_stmts_err, source from plpgsql_profiler_function_tb('test_function'); lineno | exec_stmts | exec_stmts_err | source --------+------------+----------------+------------------------------ 1 | | | 2 | 2 | 1 | begin 3 | 2 | 1 | if (a > 5) then 4 | 1 | 0 | a := a + 10; 5 | 1 | 0 | return a; 6 | | | else 7 | 1 | 1 | raise exception 'a < 5'; 8 | | | end if; 9 | | | end; (9 rows) create or replace function test_function1(a int) returns int as $$ begin if (a > 5) then a := a + 10; return a; else raise exception 'a < 5'; end if; exeception when others then raise notice 'do warning'; return -1; end; $$ language plpgsql; select test_function1(1); ERROR: a < 5 select test_function1(10); test_function1 ---------------- 20 (1 row) select lineno, exec_stmts, exec_stmts_err, source from plpgsql_profiler_function_tb('test_function1'); lineno | exec_stmts | exec_stmts_err | source --------+------------+----------------+-------------------------------- 1 | | | 2 | 2 | 1 | begin 3 | 2 | 1 | if (a > 5) then 4 | 1 | 0 | a := a + 10; 5 | 1 | 0 | return a; 6 | | | else 7 | 1 | 1 | raise exception 'a < 5'; 8 | | | end if; 9 | 0 | 0 | exeception when others then 10 | | | raise notice 'do warning'; 11 | 0 | 0 | return -1; 12 | | | end; (12 rows) drop function test_function(int); drop function test_function1(int); set plpgsql_check.profiler to off; -- 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; -- 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 warning:42804:7:assignment: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 assignment to variable "r" declared on line 4 (7 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. (5 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: SELECT a + d -- ^ Hint: No operator matches the given name and argument types. 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" (7 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: 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: #2 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #2 context: SQL statement "SELECT nested_trace_test(r)" NOTICE: #2 "a" => '0' NOTICE: #2 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #2 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #2 context: SQL statement "SELECT nested_trace_test(r)" NOTICE: #2 "a" => '1' NOTICE: #2 <<- end of function nested_trace_test (elapsed time=0.010 ms) NOTICE: #2 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #2 context: SQL statement "SELECT nested_trace_test(r)" NOTICE: #2 "a" => '2' NOTICE: #2 <<- 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 3 --> start of statement block NOTICE: #0.2 4 --> start of FOR with integer loop variable NOTICE: #0.3 6 --> start of assignment nested_trace_test(r) NOTICE: #0.3 "r" => '0' NOTICE: #2 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #2 context: SQL statement "SELECT nested_trace_test(r)" NOTICE: #2 "a" => '0' NOTICE: #2.1 2 --> start of statement block NOTICE: #2.2 3 --> start of RETURN (expr='a + 1') NOTICE: #2.2 "a" => '0' NOTICE: #2.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #2.2 <-- end of statement block (elapsed time=0.010 ms) NOTICE: #2 <<- 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 nested_trace_test(r) NOTICE: #0.3 "r" => '1' NOTICE: #2 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #2 context: SQL statement "SELECT nested_trace_test(r)" NOTICE: #2 "a" => '1' NOTICE: #2.1 2 --> start of statement block NOTICE: #2.2 3 --> start of RETURN (expr='a + 1') NOTICE: #2.2 "a" => '1' NOTICE: #2.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #2.2 <-- end of statement block (elapsed time=0.010 ms) NOTICE: #2 <<- 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 nested_trace_test(r) NOTICE: #0.3 "r" => '2' NOTICE: #2 ->> start of function nested_trace_test(integer) (oid=0) NOTICE: #2 context: SQL statement "SELECT nested_trace_test(r)" NOTICE: #2 "a" => '2' NOTICE: #2.1 2 --> start of statement block NOTICE: #2.2 3 --> start of RETURN (expr='a + 1') NOTICE: #2.2 "a" => '2' NOTICE: #2.1 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #2.2 <-- end of statement block (elapsed time=0.010 ms) NOTICE: #2 <<- 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 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) NOTICE: #0 "b" => '3' NOTICE: #0.1 3 --> start of statement block NOTICE: #0.2 4 --> start of FOR with integer loop variable NOTICE: #0.3 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.5 9 --> start of RETURN 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) NOTICE: #0 "b" => '3' NOTICE: #0.1 3 --> start of statement block NOTICE: #0.2 4 --> start of FOR with integer loop variable NOTICE: #0.3 6 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.2 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #2.3 4 --> start of RETURN (expr='a + 1') NOTICE: #2.3 "a" => '0' NOTICE: #2.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #2.3 4 --> start of RETURN (expr='a + 1') NOTICE: #2.3 "a" => '1' NOTICE: #2.2 <-- end of RETURN (elapsed time=0.010 ms) NOTICE: #2.3 4 --> start of RETURN (expr='a + 1') NOTICE: #2.3 "a" => '2' NOTICE: #2.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 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) NOTICE: #0 "$1" => '4' NOTICE: #0.1 3 --> start of statement block NOTICE: #0.2 4 --> start of FOR with integer loop variable NOTICE: #0.3 5 --> start of assignment r + 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 + 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 + 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 + 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 + 10 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 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) NOTICE: #0 "$1" => '4' NOTICE: #0.1 3 --> start of statement block NOTICE: #0.2 4 --> start of FOR with integer loop variable NOTICE: #0.3 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.5 8 --> start of assignment r + 10 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 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) NOTICE: #0 "$1" => '4' NOTICE: #0.1 3 --> start of statement block NOTICE: #0.2 4 --> start of perform plpgsql_check_pragma('disable: .. NOTICE: #0.1 <-- end of PERFORM (elapsed time=0.010 ms) NOTICE: #0.6 12 --> start of assignment r + 10 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 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); -- end from plpgsql_check_active-12