LOAD 'plpgsql'; CREATE EXTENSION IF NOT EXISTS plpgsql_check; NOTICE: extension "plpgsql_check" already exists, skipping 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 | f1 | 7 | RAISE | 42601 | too few parameters specified for RAISE | | | error | | | (3 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(); f1 ---- (1 row) select * from plpgsql_check_function_tb('f1()'); functionid | lineno | statement | sqlstate | message | detail | hint | level | position | query | context ------------+--------+-----------+----------+-----------------------------------------+--------+------+-------+----------+-------+--------- f1 | 4 | RAISE | 42601 | too many parameters specified for RAISE | | | error | | | (1 row) select f1(); f1 ---- (1 row) drop function f1(); create or replace function f1() returns void as $$ begin if false then raise notice '% %'; 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 | 4 | RAISE | 42601 | too few parameters specified for RAISE | | | error | | | (1 row) select f1(); f1 ---- (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_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 ------------------------ (0 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 ------------------------ (0 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(); drop table t1tab; 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 table t1; 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 -------------------------------------------------------------------------- performance:42804:9:SQL statement:target type has type modificator Hint: Usage of type modificator enforces slower IO casting. error:42703:13:SQL statement:record "new" has no field "status_from_xxx" (3 rows) 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 -------------------------------------------------------------------- performance:42804:5:SQL statement:target type has type modificator Hint: Usage of type modificator enforces slower IO casting. (2 rows) 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 ----------------------------------------------------------------- performance:42804:5:assignment:target type has type modificator Hint: Usage of type modificator enforces slower IO casting. (2 rows) drop function fx2();