load 'plpgsql'; load 'plpgsql_check'; 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 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 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 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 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 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(); ERROR: record "_exception" has no field "hint" CONTEXT: PL/pgSQL function f1() line 7 at GET DIAGNOSTICS 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(); NOTICE: function "f1()" was checked already CONTEXT: PL/pgSQL function f1() during function entry 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; drop type _exception_type; 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 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