load 'plpgsql'; load 'plpgsql_check'; -- 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 statement "SELECT 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 statement "SELECT 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: SELECT a + b ^ QUERY: SELECT 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: SELECT c+10 ^ QUERY: SELECT c+10 CONTEXT: PL/pgSQL function f1() line 5 at assignment to element of 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: subscripted object is not an array CONTEXT: PL/pgSQL function f1() line 5 at assignment to element of 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) 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). 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. 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 "SELECT 1,3" returned 2 columns -- 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();