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:  PL/pgSQL 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:  PL/pgSQL 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 "b" (position 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();
set plpgsql_check.mode = 'every_start';
create or replace procedure proc_test()
as $$
begin
  commit;
end;
$$ language plpgsql;
call proc_test();
drop procedure proc_test();
-- should not to crash
set plpgsql_check.mode = 'fresh_start';
do $$
begin
end;
$$;