load 'plpgsql'; load 'plpgsql_check'; set client_min_messages to notice; -- enforce context's displaying -- emulate pre 9.6 behave \set SHOW_CONTEXT always set plpgsql_check.mode = 'every_start'; create table t1(a int, b int); create function f1() returns void as $$ begin if false then update t1 set c = 30; end if; end; $$ language plpgsql; select f1(); ERROR: column "c" of relation "t1" does not exist LINE 1: update t1 set c = 30 ^ QUERY: update t1 set c = 30 CONTEXT: PL/pgSQL function f1() line 4 at SQL statement 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(); ERROR: INSERT is not allowed in a non volatile function LINE 1: insert into t1 values(10,20) ^ QUERY: insert into t1 values(10,20) CONTEXT: PL/pgSQL function f1() line 4 at SQL statement 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(); ERROR: record "r" has no field "c" CONTEXT: SQL expression "r.c" PL/pgSQL function f1() line 6 at RAISE 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(); ERROR: record "r" has no field "c" CONTEXT: SQL expression "r.c" PL/pgSQL function f1() line 6 at RAISE 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(); ERROR: record "r" has no field "c" CONTEXT: PL/pgSQL function f1() line 6 at assignment to field "c" of variable "r" declared on line 2 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(); ERROR: column "a" does not exist LINE 1: r := a + b ^ QUERY: r := a + b CONTEXT: PL/pgSQL function f1() line 5 at assignment to variable "r" declared on line 2 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(); ERROR: column "c" does not exist LINE 1: r[c+10] := 20 ^ QUERY: r[c+10] := 20 CONTEXT: PL/pgSQL function f1() line 5 at assignment to variable "r" declared on line 2 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; select f1(); ERROR: cannot subscript type integer because it does not support subscripting LINE 1: r[10] := 20 ^ QUERY: r[10] := 20 CONTEXT: PL/pgSQL function f1() line 5 at assignment to variable "r" declared on line 2 drop function f1(); create or replace function f1() returns void as $$ begin if false then insert into badbadtable values(10,20); end if; return; end; $$ language plpgsql; set plpgsql_check.mode = 'fresh_start'; select f1(); ERROR: relation "badbadtable" does not exist LINE 1: insert into badbadtable values(10,20) ^ QUERY: insert into badbadtable values(10,20) CONTEXT: PL/pgSQL function f1() line 4 at SQL statement -- should not raise exception there select f1(); f1 ---- (1 row) create or replace function f1() returns void as $$ begin if false then insert into badbadtable values(10,20); end if; return; end; $$ language plpgsql; -- after refreshing it should to raise exception again select f1(); ERROR: relation "badbadtable" does not exist LINE 1: insert into badbadtable values(10,20) ^ QUERY: insert into badbadtable values(10,20) CONTEXT: PL/pgSQL function f1() line 4 at SQL statement set plpgsql_check.mode = 'every_start'; -- should to raise warning only set plpgsql_check.fatal_errors = false; select f1(); WARNING: relation "badbadtable" does not exist LINE 1: insert into badbadtable values(10,20) ^ QUERY: insert into badbadtable values(10,20) CONTEXT: PL/pgSQL function f1() line 4 at SQL statement PL/pgSQL function f1() line 4 at SQL statement f1 ---- (1 row) drop function f1(); create function f1() returns setof t1 as $$ begin if false then return query select a,a,a from t1; return; end if; end; $$ language plpgsql; select * from f1(); WARNING: structure of query does not match function result type DETAIL: Number of returned columns (3) does not match expected column count (2). CONTEXT: PL/pgSQL function f1() line 4 at RETURN QUERY PL/pgSQL function f1() line 4 at RETURN QUERY a | b ---+--- (0 rows) drop function f1(); create function f1() returns setof t1 as $$ begin if false then return query select a, b::numeric from t1; return; end if; end; $$ language plpgsql; select * from f1(); WARNING: structure of query does not match function result type DETAIL: Returned type numeric does not match expected type integer in column 2. CONTEXT: PL/pgSQL function f1() line 4 at RETURN QUERY PL/pgSQL function f1() line 4 at RETURN QUERY a | b ---+--- (0 rows) drop function f1(); drop table t1; do $$ declare begin if false then for i in 1,3..(2) loop raise notice 'foo %', i; end loop; end if; end; $$; WARNING: query "1,3" returned 2 columns CONTEXT: PL/pgSQL function inline_code_block line 5 at FOR with integer loop variable PL/pgSQL function inline_code_block line 5 at FOR with integer loop variable -- tests designed for 9.2 set check_function_bodies to off; create or replace function f1() returns void as $$ begin if false then raise notice '%', 1, 2; end if; end; $$ language plpgsql; select f1(); ERROR: too many parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "f1" near line 4 drop function f1(); create or replace function f1() returns void as $$ begin if false then raise notice '% %'; end if; end; $$ language plpgsql; select f1(); ERROR: too few parameters specified for RAISE CONTEXT: compilation of PL/pgSQL function "f1" near line 4 drop function f1(); create type _exception_type as ( state text, message text, detail text); create or replace function f1() returns void as $$ declare _exception record; begin _exception := NULL::_exception_type; exception when others then get stacked diagnostics _exception.state = RETURNED_SQLSTATE, _exception.message = MESSAGE_TEXT, _exception.detail = PG_EXCEPTION_DETAIL, _exception.hint = PG_EXCEPTION_HINT; end; $$ language plpgsql; select f1(); WARNING: record "_exception" has no field "hint" CONTEXT: PL/pgSQL function f1() line 7 at GET STACKED DIAGNOSTICS PL/pgSQL function f1() line 7 at GET STACKED DIAGNOSTICS f1 ---- (1 row) drop function f1(); drop type _exception_type; create table footab(a int, b int, c int); create or replace function footab_trig_func() returns trigger as $$ declare x int; begin if false then -- should be ok; select count(*) from newtab into x; -- should fail; select count(*) from newtab where d = 10 into x; end if; return null; end; $$ language plpgsql; create trigger footab_trigger after insert on footab referencing new table as newtab for each statement execute procedure footab_trig_func(); -- should to fail insert into footab values(1,2,3); WARNING: column "d" does not exist LINE 1: select count(*) from newtab where d = 10 ^ QUERY: select count(*) from newtab where d = 10 CONTEXT: PL/pgSQL function footab_trig_func() line 9 at SQL statement PL/pgSQL function footab_trig_func() line 9 at SQL statement create or replace function footab_trig_func() returns trigger as $$ declare x int; begin if false then -- should be ok; select count(*) from newtab into x; end if; return null; end; $$ language plpgsql; -- should be ok insert into footab values(1,2,3); drop table footab; drop function footab_trig_func();