plpgsql_check

pig repo add pgdg -u          # Add PGDG repo and update cache

shared_preload_libraries = 'plpgsql_check';

CREATE EXTENSION plpgsql_check CASCADE;  -- requires: plpgsql

CREATE EXTENSION plpgsql_check;

SELECT * FROM plpgsql_check_function('my_function()');
SELECT * FROM plpgsql_check_function('my_function(int, text)');
SELECT * FROM plpgsql_check_function('my_function()', fatal_errors := false);

SELECT * FROM plpgsql_check_function('fx()', format := 'text');
SELECT * FROM plpgsql_check_function('fx()', format := 'json');
SELECT * FROM plpgsql_check_function('fx()', format := 'xml');

-- Trigger functions need the associated table
SELECT * FROM plpgsql_check_function('my_trigger_func()', 'my_table');

-- With transition tables
SELECT * FROM plpgsql_check_function(
  'my_trigger_func()', 'my_table',
  newtable := 'newtab', oldtable := 'oldtab'
);

SELECT * FROM plpgsql_check_function('fx()',
  extra_warnings := true,         -- dead code, unused parameters
  performance_warnings := true,   -- index and casting issues
  security_warnings := true,      -- SQL injection checks
  compatibility_warnings := true  -- obsolete patterns
);

SELECT p.oid, p.proname, plpgsql_check_function(p.oid)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279;

LOAD 'plpgsql_check';
SET plpgsql_check.mode = 'every_start';  -- check before each execution

-- Enable profiling
SELECT plpgsql_check_profiler(true);

-- Execute functions to collect data
SELECT my_function();

-- View per-line execution times
SELECT lineno, avg_time, source
FROM plpgsql_profiler_function_tb('my_function()');

-- Per-statement profile
SELECT stmtid, parent_stmtid, lineno, exec_stmts, stmtname
FROM plpgsql_profiler_function_statements_tb('my_function()');

-- All function statistics
SELECT * FROM plpgsql_profiler_functions_all();

-- Reset profiling data
SELECT plpgsql_profiler_reset_all();

SELECT * FROM plpgsql_show_dependency_tb('my_function(int)');

SELECT * FROM plpgsql_coverage_statements('my_function()');
SELECT * FROM plpgsql_coverage_branches('my_function()');

CREATE OR REPLACE FUNCTION fx(anyelement) RETURNS text AS $$
BEGIN
  /* @plpgsql_check_options: anyelementtype = text */
  RETURN $1;
END;
$$ LANGUAGE plpgsql;