load 'plpgsql'; create extension if not exists plpgsql_check; -- -- 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 CONTEXT: PL/pgSQL function plpgsql_check_function_tb(regprocedure,regclass,boolean,boolean,boolean,boolean) line 3 at RETURN QUERY 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 | | insert into t1 values(10,20) | f1 | 5 | SQL statement | 0A000 | UPDATE is not allowed in a non volatile function | | | error | | update t1 set a = 10 | f1 | 6 | SQL statement | 0A000 | DELETE is not allowed in a non volatile function | | | error | | delete from t1 | (3 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; 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 | | 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 | | | (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 | (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 | (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 | | | (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 | | | (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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 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 attributies for target variables | There are less target variables than output columns in query. | Check target variables in SELECT INTO statement | warning | | | (1 row) 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 attributies for target variables | There are more target variables than output columns in query. | Check target variables in SELECT INTO statement. | warning | | | (1 row) -- 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" (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('f1()'); plpgsql_check_function ---------------------------------------------------- error:42703:5:assignment:column "a" does not exist Query: SELECT a + b -- ^ (3 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 -- ^ (3 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 (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('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" (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('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 ------------------------ (0 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 attributies for target variables Detail: There are less target variables than output columns in query. Hint: Check target variables in SELECT INTO statement (3 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 attributies for target variables Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. (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('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:unused parameter "_input1" warning extra:00000:unmodified OUT variable "_output1" warning extra:00000:unmodified OUT variable "_output2" (4 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 ------------------------ (0 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. (3 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 ------------------------ (0 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 ------------------------ (0 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:5: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. 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. 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. 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! 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. (18 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 ------------------------ (0 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. (3 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 -------------------------------------------------------------------------------------- warning:42804:3:RETURN: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. (3 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! (3 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 ------------------------ (0 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. (3 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 ------------------------ (0 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. (3 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 ------------------------ (0 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 (1 row) 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 ------------------------ (0 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 | | | (1 row) 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 | | | (1 row) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 | | | (1 row) 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 | | | (1 row) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 | | | (1 row) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 rows) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 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 | | | (2 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 | | | (1 row) 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 ------------+--------+-----------+----------+---------+--------+------+-------+----------+-------+--------- (0 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 | | | (1 row) 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 to a scalar type (1 row) 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 ------------------------ (0 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 ------------------------ (0 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" (1 row) 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 -------------------------------------------------------------------------------------- warning:42804:3: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. (3 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. (3 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 ------------------------ (0 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.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" (2 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 attributies 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 attributies 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 attributies for target variables Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. (9 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! (3 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 ------------------------ (0 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" (2 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: cannot begin/end transactions in PL/pgSQL HINT: Use a BEGIN block with an EXCEPTION clause instead. CONTEXT: PL/pgSQL function fxx() line 3 at SQL statement select * from plpgsql_check_function('fxx()'); plpgsql_check_function ------------------------ (0 rows) 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:unused parameter "a" (6 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();