-- -- regress tests of generic PL/pgSQL statement walker (src/generic_stmt_walker.c) -- -- The walker itself has no SQL interface. It is used by the profiler -- (statement and branch coverage, per statement statistics, queryid -- retrieval), so the profiler API is used here as a probe. -- load 'plpgsql_check'; set client_min_messages to warning; create extension if not exists plpgsql_check; set client_min_messages to notice; create table swt_tab(a int, b int); insert into swt_tab values(1,1),(2,2),(3,3); create procedure swt_proc(a int) as $$ begin end; $$ language plpgsql; -- -- A function using every statement type known to the walker. The -- statements are executed, so the profiler collects statistics for all -- of them. -- create function swt_all_stmts(par int) returns int as $$ declare i int; j int; r record; arr int[] = array[1, 2, 3]; c refcursor; cc cursor(p int) for select a from swt_tab where a = p; res int = 0; begin -- IF with two ELSIF branches and an ELSE branch if par = 1 then res := res + 1; elsif par = 2 then res := res + 2; elsif par = 3 then res := res + 3; else res := res + 4; end if; -- IF without ELSE branch (hypothetical else branch of coverage) if par > 100 then res := res + 1; end if; -- simple CASE - the tested expression is the statement's expression case par when 1 then res := res + 1; when 2 then res := res + 2; else res := res + 3; end case; -- searched CASE - has no tested expression case when par > 0 then res := res + 1; else res := res + 2; end case; -- unconditional LOOP terminated by conditional EXIT i := 0; loop i := i + 1; exit when i >= 2; end loop; -- WHILE loop while i < 4 loop i := i + 1; end loop; -- FOR over integer range, with an explicit BY step for j in reverse 10 .. 1 by 3 loop res := res + 1; end loop; -- FOR over a static query for r in select a, b from swt_tab order by a loop res := res + r.a; end loop; -- FOR over a bound cursor (the argument list is the statement's query) for r in cc(1) loop res := res + r.a; end loop; -- FOR over a dynamic query with USING parameters for r in execute 'select a from swt_tab where a > $1' using 0 loop res := res + r.a; end loop; -- FOREACH over an array foreach i in array arr loop res := res + i; end loop; -- OPEN of a bound cursor, FETCH, CLOSE open cc(2); fetch cc into r; close cc; -- OPEN of an unbound cursor for a static query open c for select a from swt_tab; fetch c into i; close c; -- OPEN of an unbound cursor for a dynamic query with USING parameters open c for execute 'select a from swt_tab where a > $1' using 0; fetch c into i; close c; -- PERFORM perform count(*) from swt_tab; -- CALL call swt_proc(1); -- plain SQL statements insert into swt_tab values(10, 10); update swt_tab set b = 11 where a = 10; delete from swt_tab where a = 10; -- dynamic SQL with USING parameters execute 'delete from swt_tab where a = $1' using -1; -- GET DIAGNOSTICS has no interesting substructure get diagnostics i = row_count; -- ASSERT with a message expression assert res is not null, 'res is null for ' || par; -- RAISE with format parameters and with options, handled by a nested -- block with an exception handler begin raise exception 'raised for %', par using errcode = 'division_by_zero', hint = 'no hint ' || par; exception when division_by_zero then res := res + 1; end; return res; end; $$ language plpgsql; -- -- RETURN NEXT and both flavours of RETURN QUERY live in a set returning -- function. -- create function swt_setof() returns setof int as $$ declare i int; begin for i in 1 .. 2 loop return next i; end loop; return query select a from swt_tab order by a limit 1; return query execute 'select b from swt_tab where b = $1' using 2; return; end; $$ language plpgsql; -- -- COMMIT and ROLLBACK are allowed in procedures only. -- create procedure swt_txn() as $$ begin commit; rollback; end; $$ language plpgsql; set plpgsql_check.profiler to on; select swt_all_stmts(1); swt_all_stmts --------------- 27 (1 row) select swt_all_stmts(2); swt_all_stmts --------------- 29 (1 row) select swt_all_stmts(10); swt_all_stmts --------------- 32 (1 row) select * from swt_setof(); swt_setof ----------- 1 2 1 2 (4 rows) call swt_txn(); set plpgsql_check.profiler to off; -- the walker is used to assign the statement statistics to the AST select stmtid, parent_stmtid, block_num, lineno, exec_stmts, stmtname from plpgsql_profiler_function_statements_tb('swt_all_stmts'); stmtid | parent_stmtid | block_num | lineno | exec_stmts | stmtname --------+---------------+-----------+--------+------------+-------------------------------- 1 | | 1 | 10 | 3 | statement block 2 | 1 | 1 | 12 | 3 | IF 3 | 2 | 1 | 13 | 1 | assignment 4 | 2 | 2 | 15 | 1 | assignment 5 | 2 | 3 | 17 | 0 | assignment 6 | 2 | 4 | 19 | 1 | assignment 7 | 1 | 2 | 23 | 3 | IF 8 | 7 | 1 | 24 | 0 | assignment 9 | 1 | 3 | 28 | 3 | CASE 10 | 9 | 1 | 29 | 1 | assignment 11 | 9 | 2 | 30 | 1 | assignment 12 | 9 | 3 | 31 | 1 | assignment 13 | 1 | 4 | 35 | 3 | CASE 14 | 13 | 1 | 36 | 3 | assignment 15 | 13 | 2 | 37 | 0 | assignment 16 | 1 | 5 | 41 | 3 | assignment 17 | 1 | 6 | 42 | 3 | LOOP 18 | 17 | 1 | 43 | 6 | assignment 19 | 17 | 2 | 44 | 6 | EXIT 20 | 1 | 7 | 48 | 3 | WHILE 21 | 20 | 1 | 49 | 6 | assignment 22 | 1 | 8 | 53 | 3 | FOR with integer loop variable 23 | 22 | 1 | 54 | 12 | assignment 24 | 1 | 9 | 58 | 3 | FOR over SELECT rows 25 | 24 | 1 | 59 | 9 | assignment 26 | 1 | 10 | 63 | 3 | FOR over cursor 27 | 26 | 1 | 64 | 3 | assignment 28 | 1 | 11 | 68 | 3 | FOR over EXECUTE statement 29 | 28 | 1 | 69 | 9 | assignment 30 | 1 | 12 | 73 | 3 | FOREACH over array 31 | 30 | 1 | 74 | 9 | assignment 32 | 1 | 13 | 78 | 3 | OPEN 33 | 1 | 14 | 79 | 3 | FETCH 34 | 1 | 15 | 80 | 3 | CLOSE 35 | 1 | 16 | 83 | 3 | OPEN 36 | 1 | 17 | 84 | 3 | FETCH 37 | 1 | 18 | 85 | 3 | CLOSE 38 | 1 | 19 | 88 | 3 | OPEN 39 | 1 | 20 | 89 | 3 | FETCH 40 | 1 | 21 | 90 | 3 | CLOSE 41 | 1 | 22 | 93 | 3 | PERFORM 42 | 1 | 23 | 96 | 3 | CALL 43 | 1 | 24 | 99 | 3 | SQL statement 44 | 1 | 25 | 100 | 3 | SQL statement 45 | 1 | 26 | 101 | 3 | SQL statement 46 | 1 | 27 | 104 | 3 | EXECUTE 47 | 1 | 28 | 107 | 3 | GET DIAGNOSTICS 48 | 1 | 29 | 110 | 3 | ASSERT 49 | 1 | 30 | 114 | 3 | statement block 50 | 49 | 1 | 115 | 3 | RAISE 51 | 49 | 2 | 118 | 3 | assignment 52 | 1 | 31 | 121 | 3 | RETURN (52 rows) select stmtid, parent_stmtid, block_num, lineno, exec_stmts, stmtname from plpgsql_profiler_function_statements_tb('swt_setof'); stmtid | parent_stmtid | block_num | lineno | exec_stmts | stmtname --------+---------------+-----------+--------+------------+-------------------------------- 1 | | 1 | 4 | 1 | statement block 2 | 1 | 1 | 5 | 1 | FOR with integer loop variable 3 | 2 | 1 | 6 | 2 | RETURN NEXT 4 | 1 | 2 | 9 | 1 | RETURN QUERY 5 | 1 | 3 | 10 | 1 | RETURN QUERY 6 | 1 | 4 | 12 | 1 | RETURN (6 rows) select stmtid, parent_stmtid, block_num, lineno, exec_stmts, stmtname from plpgsql_profiler_function_statements_tb('swt_txn'); stmtid | parent_stmtid | block_num | lineno | exec_stmts | stmtname --------+---------------+-----------+--------+------------+----------------- 1 | | 1 | 2 | 1 | statement block 2 | 1 | 1 | 3 | 1 | COMMIT 3 | 1 | 2 | 4 | 1 | ROLLBACK (3 rows) -- every loop kind has to be recognized as a branch, and the walker has -- to be able to return the body of every loop kind select plpgsql_coverage_statements('swt_all_stmts'); plpgsql_coverage_statements ----------------------------- 0.9423076923076923 (1 row) select plpgsql_coverage_branches('swt_all_stmts'); plpgsql_coverage_branches --------------------------- 0.85 (1 row) select plpgsql_coverage_statements('swt_setof'); plpgsql_coverage_statements ----------------------------- 1 (1 row) select plpgsql_coverage_branches('swt_setof'); plpgsql_coverage_branches --------------------------- 1 (1 row) select plpgsql_coverage_statements('swt_txn'); plpgsql_coverage_statements ----------------------------- 1 (1 row) select plpgsql_coverage_branches('swt_txn'); plpgsql_coverage_branches --------------------------- 1 (1 row) -- -- The expression returned by the walker for a statement is used for the -- queryid retrieval. The fake queryid hook stores the command type of the -- query, so the result shows which statements carry a query. -- create function swt_queryid() returns void as $$ declare r record; c refcursor; cc cursor(p int) for select a from swt_tab where a = p; tabname text = 'swt_tab'; i int; begin insert into swt_tab values(20, 20); update swt_tab set b = 21 where a = 20; delete from swt_tab where a = 20; select count(*) into i from swt_tab; perform count(*) from swt_tab; for r in select a from swt_tab order by a loop null; end loop; for r in cc(1) loop null; end loop; for r in execute 'select a from ' || tabname loop null; end loop; open c for select a from swt_tab; fetch c into i; close c; open c for execute 'select a from ' || tabname; fetch c into i; close c; execute 'delete from ' || tabname || ' where a = $1' using -1; end; $$ language plpgsql; create function swt_queryid_setof() returns setof int as $$ declare tabname text = 'swt_tab'; begin return query select a from swt_tab order by a; return query execute 'select b from ' || tabname || ' order by b'; end; $$ language plpgsql; set plpgsql_check.profiler to on; 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 swt_queryid(); swt_queryid ------------- (1 row) select * from swt_queryid_setof(); swt_queryid_setof ------------------- 1 2 3 1 2 3 (6 rows) select plpgsql_profiler_remove_fake_queryid_hook(); plpgsql_profiler_remove_fake_queryid_hook ------------------------------------------- (1 row) select lineno, stmt_lineno, queryids, exec_stmts, source from plpgsql_profiler_function_tb('swt_queryid'); lineno | stmt_lineno | queryids | exec_stmts | source --------+-------------+----------+------------+------------------------------------------------------------------ 1 | | | | 2 | | | | declare 3 | | | | r record; 4 | | | | c refcursor; 5 | | | | cc cursor(p int) for select a from swt_tab where a = p; 6 | | | | tabname text = 'swt_tab'; 7 | | | | i int; 8 | 8 | | {1} | begin 9 | 9 | {3} | {1} | insert into swt_tab values(20, 20); 10 | 10 | {2} | {1} | update swt_tab set b = 21 where a = 20; 11 | 11 | {4} | {1} | delete from swt_tab where a = 20; 12 | | | | 13 | 13 | {1} | {1} | select count(*) into i from swt_tab; 14 | 14 | {1} | {1} | perform count(*) from swt_tab; 15 | | | | 16 | 16 | {1} | {1} | for r in select a from swt_tab order by a loop 17 | | | | null; 18 | | | | end loop; 19 | | | | 20 | 20 | {1} | {1} | for r in cc(1) loop 21 | | | | null; 22 | | | | end loop; 23 | | | | 24 | 24 | {1} | {1} | for r in execute 'select a from ' || tabname loop 25 | | | | null; 26 | | | | end loop; 27 | | | | 28 | 28 | {1} | {1} | open c for select a from swt_tab; 29 | 29 | | {1} | fetch c into i; 30 | 30 | | {1} | close c; 31 | | | | 32 | 32 | {1} | {1} | open c for execute 'select a from ' || tabname; 33 | 33 | | {1} | fetch c into i; 34 | 34 | | {1} | close c; 35 | | | | 36 | 36 | {4} | {1} | execute 'delete from ' || tabname || ' where a = $1' using -1; 37 | | | | end; (37 rows) select lineno, stmt_lineno, queryids, exec_stmts, source from plpgsql_profiler_function_tb('swt_queryid_setof'); lineno | stmt_lineno | queryids | exec_stmts | source --------+-------------+----------+------------+---------------------------------------------------------------------- 1 | | | | 2 | | | | declare 3 | | | | tabname text = 'swt_tab'; 4 | 4 | | {1} | begin 5 | 5 | {1} | {1} | return query select a from swt_tab order by a; 6 | 6 | {1} | {1} | return query execute 'select b from ' || tabname || ' order by b'; 7 | | | | end; (7 rows) set plpgsql_check.profiler to off; drop function swt_queryid_setof(); drop function swt_queryid(); drop procedure swt_txn(); drop function swt_setof(); drop function swt_all_stmts(int); drop procedure swt_proc(int); drop table swt_tab;