uplpgsql/test/plpgsql/sql/plpgsql_control.sql at main · jdatcmd/uplpgsql

848 lines (761 loc) · 21.9 KB
-- Tests for PL/pgSQL control structures
-- integer FOR loop
do LANGUAGE uplpgsql $$
  -- basic case
  for i in 1..3 loop
    raise notice '1..3: i = %', i;
  end loop;
  -- with BY, end matches exactly
  for i in 1..10 by 3 loop
    raise notice '1..10 by 3: i = %', i;
  end loop;
  -- with BY, end does not match
  for i in 1..11 by 3 loop
    raise notice '1..11 by 3: i = %', i;
  end loop;
  -- zero iterations
  for i in 1..0 by 3 loop
    raise notice '1..0 by 3: i = %', i;
  end loop;
  -- REVERSE
  for i in reverse 10..0 by 3 loop
    raise notice 'reverse 10..0 by 3: i = %', i;
  end loop;
  -- potential overflow
  for i in 2147483620..2147483647 by 10 loop
    raise notice '2147483620..2147483647 by 10: i = %', i;
  end loop;
  -- potential overflow, reverse direction
  for i in reverse -2147483620..-2147483647 by 10 loop
    raise notice 'reverse -2147483620..-2147483647 by 10: i = %', i;
  end loop;
-- BY can't be zero or negative
do LANGUAGE uplpgsql $$
  for i in 1..3 by 0 loop
    raise notice '1..3 by 0: i = %', i;
  end loop;
do LANGUAGE uplpgsql $$
  for i in 1..3 by -1 loop
    raise notice '1..3 by -1: i = %', i;
  end loop;
do LANGUAGE uplpgsql $$
  for i in reverse 1..3 by -1 loop
    raise notice 'reverse 1..3 by -1: i = %', i;
  end loop;
-- CONTINUE statement
create table conttesttbl(idx serial, v integer);
insert into conttesttbl(v) values(10);
insert into conttesttbl(v) values(20);
insert into conttesttbl(v) values(30);
insert into conttesttbl(v) values(40);
create function continue_test1() returns void as $$
declare _i integer = 0; _r record;
  raise notice '---1---';
    _i := _i + 1;
    raise notice '%', _i;
    continue when _i < 10;
  end loop;
  raise notice '---2---';
    _i := _i - 1;
      raise notice '%', _i;
      continue lbl when _i > 0;
      exit lbl;
    end loop;
  end loop;
  raise notice '---3---';
  <<the_loop>>
  while _i < 10 loop
    _i := _i + 1;
    continue the_loop when _i % 2 = 0;
    raise notice '%', _i;
  end loop;
  raise notice '---4---';
  for _i in 1..10 loop
      -- applies to outer loop, not the nested begin block
      continue when _i < 5;
      raise notice '%', _i;
  end loop;
  raise notice '---5---';
  for _r in select * from conttesttbl loop
    continue when _r.v <= 20;
    raise notice '%', _r.v;
  end loop;
  raise notice '---6---';
  for _r in execute 'select * from conttesttbl' loop
    continue when _r.v <= 20;
    raise notice '%', _r.v;
  end loop;
  raise notice '---7---';
  <<looplabel>>
  for _i in 1..3 loop
    continue looplabel when _i = 2;
    raise notice '%', _i;
  end loop;
  raise notice '---8---';
  while _i <= 3 loop
    raise notice '%', _i;
    _i := _i + 1;
    continue when _i = 3;
  end loop;
  raise notice '---9---';
  for _r in select * from conttesttbl order by v limit 1 loop
    raise notice '%', _r.v;
    continue;
  end loop;
  raise notice '---10---';
  for _r in execute 'select * from conttesttbl order by v limit 1' loop
    raise notice '%', _r.v;
    continue;
  end loop;
  raise notice '---11---';
  <<outerlooplabel>>
  for _i in 1..2 loop
    raise notice 'outer %', _i;
    <<innerlooplabel>>
    for _j in 1..3 loop
      continue outerlooplabel when _j = 2;
      raise notice 'inner %', _j;
    end loop;
  end loop;
end; $$ LANGUAGE uplpgsql;
select continue_test1();
-- should fail: CONTINUE is only legal inside a loop
create function continue_error1() returns void as $$
        continue;
$$ LANGUAGE uplpgsql;
-- should fail: unlabeled EXIT is only legal inside a loop
create function exit_error1() returns void as $$
        exit;
$$ LANGUAGE uplpgsql;
-- should fail: no such label
create function continue_error2() returns void as $$
        loop
            continue no_such_label;
        end loop;
$$ LANGUAGE uplpgsql;
-- should fail: no such label
create function exit_error2() returns void as $$
        loop
            exit no_such_label;
        end loop;
$$ LANGUAGE uplpgsql;
-- should fail: CONTINUE can't reference the label of a named block
create function continue_error3() returns void as $$
    <<begin_block1>>
        loop
            continue begin_block1;
        end loop;
$$ LANGUAGE uplpgsql;
-- On the other hand, EXIT *can* reference the label of a named block
create function exit_block1() returns void as $$
    <<begin_block1>>
        loop
            exit begin_block1;
            raise exception 'should not get here';
        end loop;
$$ LANGUAGE uplpgsql;
select exit_block1();
-- EXIT with a block label.
-- A labeled BEGIN...END is an EXIT target just as a loop is, so codegen has to
-- push it onto the same stack -- but only as an EXIT target: an unlabeled
-- EXIT/CONTINUE must look straight through it to the innermost real loop, and
-- CONTINUE may never name a block.  When the block carries an exception
-- handler the jump also has to run the subtransaction release on its way out
-- rather than branch past it.
create table exitblk_t (v int);
-- control resumes after the block, skipping the rest of its body
create function exit_block_resumes() returns int language uplpgsql as $$
declare r int := 0;
  <<b>> begin r := 1; exit b; r := 99; end;
  r := r + 10;
  return r;
select exit_block_resumes();
-- EXIT unwinds out of nested loops to the block label
create function exit_block_nested_loop() returns int language uplpgsql as $$
declare r int := 0;
  <<b>> begin
    for i in 1..10 loop
      for j in 1..10 loop r := r + 1; exit b; end loop;
    end loop;
  return r;
select exit_block_nested_loop();
-- conditional EXIT on a block label
create function exit_block_when() returns int language uplpgsql as $$
declare r int := 0;
  <<b>> begin
    for i in 1..10 loop r := r + 1; exit b when i = 4; end loop;
  return r;
select exit_block_when();
-- an unlabeled EXIT inside a labeled block still targets the enclosing loop
create function exit_unlabeled_in_block() returns int language uplpgsql as $$
declare r int := 0;
  for i in 1..5 loop
    <<b>> begin r := r + 1; exit; end;
  end loop;
  return r;
select exit_unlabeled_in_block();
-- inner vs outer block labels resolve independently
create function exit_outer_from_inner() returns int language uplpgsql as $$
declare r int := 0;
  <<o>> begin <<i>> begin r := 1; exit o; end; r := 99; end;
  return r;
select exit_outer_from_inner();
create function exit_inner_only() returns int language uplpgsql as $$
declare r int := 0;
  <<o>> begin <<i>> begin r := 1; exit i; r := 50; end; r := r + 5; end;
  return r;
select exit_inner_only();
-- CONTINUE may not name a block label
create function continue_block_label() returns int language uplpgsql as $$
  <<b>> begin loop continue b; end loop; end;
  return 1;
-- EXIT must name a label that actually encloses the statement
create function exit_no_such_label() returns int language uplpgsql as $$
  <<b>> begin exit zzz; end;
  return 1;
-- EXIT out of a block that has an exception handler must still release the
-- subtransaction: the inserted row has to survive
create function exit_block_exc_subxact() returns int language uplpgsql as $$
  <<b>> begin
    insert into exitblk_t values (5);
    exit b;
  exception when others then null;
  return (select count(*)::int from exitblk_t);
truncate exitblk_t;
select exit_block_exc_subxact();
-- EXIT from inside a handler body leaves the block, running handler cleanup on
-- the way: the failed insert rolls back, the handler's insert commits
create function exit_handler_subxact() returns int language uplpgsql as $$
  <<b>> begin
    insert into exitblk_t values (1);
    raise exception 'boom';
  exception when others then
    insert into exitblk_t values (2);
    exit b;
  return (select count(*)::int from exitblk_t);
truncate exitblk_t;
select exit_handler_subxact();
-- statements after EXIT inside a handler are skipped
create function exit_from_handler_body() returns int language uplpgsql as $$
declare r int := 0;
  <<b>> begin
    raise exception 'boom';
  exception when others then
    r := 7; exit b; r := 99;
  return r + 1;
select exit_from_handler_body();
-- exception handling still works in a later block after an EXIT-ed one
create function exc_still_works_after_exit() returns int language uplpgsql as $$
declare r int := 0;
  <<b>> begin exit b; exception when others then r := 9; end;
  begin raise exception 'x'; exception when others then r := 3; end;
  return r;
select exc_still_works_after_exit();
drop function exit_block_resumes();
drop function exit_block_nested_loop();
drop function exit_block_when();
drop function exit_unlabeled_in_block();
drop function exit_outer_from_inner();
drop function exit_inner_only();
drop function exit_block_exc_subxact();
drop function exit_handler_subxact();
drop function exit_from_handler_body();
drop function exc_still_works_after_exit();
drop table exitblk_t;
-- verbose end block and end loop
create function end_label1() returns void as $$
  <<flbl1>>
  for i in 1 .. 10 loop
    raise notice 'i = %', i;
    exit flbl1;
  end loop flbl1;
  <<flbl2>>
  for j in 1 .. 10 loop
    raise notice 'j = %', j;
    exit flbl2;
  end loop;
$$ LANGUAGE uplpgsql;
select end_label1();
-- should fail: undefined end label
create function end_label2() returns void as $$
  for _i in 1 .. 10 loop
  end loop flbl1;
$$ LANGUAGE uplpgsql;
-- should fail: end label does not match start label
create function end_label3() returns void as $$
<<outer_label>>
  <<inner_label>>
  for _i in 1 .. 10 loop
  end loop outer_label;
$$ LANGUAGE uplpgsql;
-- should fail: end label on a block without a start label
create function end_label4() returns void as $$
<<outer_label>>
  for _i in 1 .. 10 loop
  end loop outer_label;
$$ LANGUAGE uplpgsql;
-- unlabeled exit matches no blocks
do LANGUAGE uplpgsql $$
for i in 1..10 loop
  <<innerblock>>
    begin  -- unlabeled block
      exit;
      raise notice 'should not get here';
    raise notice 'should not get here, either';
  raise notice 'nor here';
raise notice 'should get here';
-- check exit out of an unlabeled block to a labeled one
do LANGUAGE uplpgsql $$
<<outerblock>>
  <<innerblock>>
    <<moreinnerblock>>
      begin  -- unlabeled block
        exit innerblock;
        raise notice 'should not get here';
      raise notice 'should not get here, either';
    raise notice 'nor here';
  raise notice 'should get here';
-- check exit out of outermost block
do LANGUAGE uplpgsql $$
<<outerblock>>
  <<innerblock>>
    exit outerblock;
    raise notice 'should not get here';
  raise notice 'should not get here, either';
-- unlabeled exit does match a while loop
do LANGUAGE uplpgsql $$
  <<outermostwhile>>
  while 1 > 0 loop
    <<outerwhile>>
    while 1 > 0 loop
      <<innerwhile>>
      while 1 > 0 loop
        exit;
        raise notice 'should not get here';
      end loop;
      raise notice 'should get here';
      exit outermostwhile;
      raise notice 'should not get here, either';
    end loop;
    raise notice 'nor here';
  end loop;
  raise notice 'should get here, too';
-- check exit out of an unlabeled while to a labeled one
do LANGUAGE uplpgsql $$
  <<outerwhile>>
  while 1 > 0 loop
    while 1 > 0 loop
      exit outerwhile;
      raise notice 'should not get here';
    end loop;
    raise notice 'should not get here, either';
  end loop;
  raise notice 'should get here';
-- continue to an outer while
do LANGUAGE uplpgsql $$
declare i int := 0;
  <<outermostwhile>>
  while i < 2 loop
    raise notice 'outermostwhile, i = %', i;
    i := i + 1;
    <<outerwhile>>
    while 1 > 0 loop
      <<innerwhile>>
      while 1 > 0 loop
        continue outermostwhile;
        raise notice 'should not get here';
      end loop;
      raise notice 'should not get here, either';
    end loop;
    raise notice 'nor here';
  end loop;
  raise notice 'out of outermostwhile, i = %', i;
-- return out of a while
create function return_from_while() returns int LANGUAGE uplpgsql as $$
declare i int := 0;
  while i < 10 loop
    if i > 2 then
      return i;
    end if;
    i := i + 1;
  end loop;
  return null;
select return_from_while();
-- using list of scalars in fori and fore stmts
create function for_vect() returns void as $proc$
<<lbl>>declare a integer; b varchar; c varchar; r record;
  for i in 1 .. 3 loop
    raise notice '%', i;
  end loop;
  -- fore with record var
  for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
    raise notice '% % %', r.aa, r.bb, r.cc;
  end loop;
  -- fore with single scalar
  for a in select gs from generate_series(1,4) gs loop
    raise notice '%', a;
  end loop;
  -- fore with multiple scalars
  for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
    raise notice '% % %', a, b, c;
  end loop;
  -- using qualified names in fors, fore is enabled, disabled only for fori
  for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
    raise notice '% % %', a, b, c;
  end loop;
$proc$ LANGUAGE uplpgsql;
select for_vect();
-- CASE statement
create or replace function case_test(bigint) returns text as $$
declare a int = 10;
        b int = 1;
    when 1 then
      return 'one';
    when 2 then
      return 'two';
    when 3,4,3+5 then
      return 'three, four or eight';
    when a then
      return 'ten';
    when a+b, a+b+1 then
      return 'eleven, twelve';
  end case;
$$ LANGUAGE uplpgsql immutable;
select case_test(1);
select case_test(2);
select case_test(3);
select case_test(4);
select case_test(5); -- fails
select case_test(8);
select case_test(10);
select case_test(11);
select case_test(12);
select case_test(13); -- fails
create or replace function catch() returns void as $$
  raise notice '%', case_test(6);
  when case_not_found then
    raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
$$ LANGUAGE uplpgsql;
select catch();
-- test the searched variant too, as well as ELSE
create or replace function case_test(bigint) returns text as $$
declare a int = 10;
    when $1 = 1 then
      return 'one';
    when $1 = a + 2 then
      return 'twelve';
      return 'other';
  end case;
$$ LANGUAGE uplpgsql immutable;
select case_test(1);
select case_test(2);
select case_test(12);
select case_test(13);
-- test line comment between WHEN and THEN
create or replace function case_comment(int) returns text as $$
    when 1 -- comment before THEN
      then return 'one';
      return 'other';
  end case;
$$ LANGUAGE uplpgsql immutable;
select case_comment(1);
-- FOR ... IN <cursor> (cursor FOR loop).
-- The loop opens the cursor through uplpgsql_rt_open_forc_cursor(), which
-- wraps the Portal in the cursor context that the fetch and close helpers
-- expect.  Calling exec_open_forc_cursor() directly yields a bare Portal
-- and the fetch reads it as a context.
create or replace function forc_basic() returns int language uplpgsql as $$
declare t int := 0; rec record;
  c cursor for select g as v from generate_series(1,3) g;
  for rec in c loop t := t + rec.v; end loop;
  return t;
select forc_basic();
-- parameterised cursor
create or replace function forc_args() returns int language uplpgsql as $$
declare t int := 0; rec record;
  c cursor(n int) for select g as v from generate_series(1,n) g;
  for rec in c(4) loop t := t + rec.v; end loop;
  return t;
select forc_args();
-- RETURN out of the loop body: closes the cursor on the return path,
-- which is a separate close site from normal loop exit
create or replace function forc_return() returns int language uplpgsql as $$
declare rec record;
  c cursor for select g as v from generate_series(5,9) g;
  for rec in c loop return rec.v; end loop;
  return -1;
select forc_return();
-- empty result, and FOUND afterwards
create or replace function forc_empty() returns int language uplpgsql as $$
declare t int := 0; rec record;
  c cursor for select g as v from generate_series(1,0) g;
  for rec in c loop t := t + rec.v; end loop;
  if found then return -1; end if;
  return t;
select forc_empty();
drop function forc_basic();
drop function forc_args();
drop function forc_return();
drop function forc_empty();
-- Simple CASE over types other than integer.
-- The parser cannot know what "CASE <expr> WHEN ..." tests, so it builds the
-- temporary as an INT4 placeholder and expects the runtime to retype it to the
-- test expression's real type.  Two things have to happen for that to work:
-- the assignment must do the retyping, and the WHEN comparisons must not be
-- planned before it -- a plan prepared against the placeholder binds the
-- parameter as int4 and stays wrong.
create or replace function case_text() returns text language uplpgsql as $$
declare a text := 'x'; r text;
begin case a when 'x' then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_text();
create or replace function case_bool() returns text language uplpgsql as $$
declare a bool := true; r text;
begin case a when true then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_bool();
-- numeric is the one that returned the *wrong branch* rather than erroring,
-- because its WHEN inlined and kept the int4-bound plan
create or replace function case_numeric() returns text language uplpgsql as $$
declare a numeric := 1.5; r text;
begin case a when 1.5 then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_numeric();
create or replace function case_float8() returns text language uplpgsql as $$
declare a float8 := 1.5; r text;
begin case a when 1.5::float8 then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_float8();
create or replace function case_date() returns text language uplpgsql as $$
declare a date := '2020-01-01'; r text;
begin case a when '2020-01-01'::date then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_date();
create or replace function case_array() returns text language uplpgsql as $$
declare a int[] := array[1,2]; r text;
begin case a when array[1,2] then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_array();
-- integers must keep working
create or replace function case_int() returns text language uplpgsql as $$
declare a int := 1; r text;
begin case a when 1 then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_int();
create or replace function case_bigint() returns text language uplpgsql as $$
declare a bigint := 1; r text;
begin case a when 1 then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_bigint();
-- branch selection, WHEN lists, ELSE, no-match and a NULL test value
create or replace function case_else() returns text language uplpgsql as $$
declare a text := 'z'; r text;
begin case a when 'x' then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_else();
create or replace function case_multi() returns text language uplpgsql as $$
declare a text := 'y'; r text;
begin case a when 'x' then r := '1'; when 'y' then r := '2'; else r := '3'; end case; return r; end; $$;
select case_multi();
create or replace function case_when_list() returns text language uplpgsql as $$
declare a int := 3; r text;
begin case a when 1,2 then r := 'lo'; when 3,4 then r := 'hi'; else r := 'n'; end case; return r; end; $$;
select case_when_list();
create or replace function case_no_match() returns text language uplpgsql as $$
declare a text := 'z';
  case a when 'x' then return 'm'; end case;
  return 'no';
exception when case_not_found then return 'notfound';
select case_no_match();
create or replace function case_null_test() returns text language uplpgsql as $$
declare a text; r text;
begin case a when 'x' then r := 'm'; else r := 'n'; end case; return r; end; $$;
select case_null_test();
create or replace function case_nested() returns text language uplpgsql as $$
declare a text := 'x'; b int := 2; r text;
  case a when 'x' then
    case b when 2 then r := 'inner'; else r := 'no'; end case;
  else r := 'outer';
  end case;
  return r;
select case_nested();
-- the WHEN *bodies* must still compile normally: only the conditions are held
-- back from compile-time planning
create or replace function case_body(n int) returns bigint language uplpgsql as $$
declare a text := 'x'; t bigint := 0; i int;
  for i in 1..n loop
    case a when 'x' then
      t := t + i;
      if t > 100000 then t := 0; end if;
    else t := t - 1;
    end case;
  end loop;
  return t;
select case_body(500);
-- CASE over an array clears its temporary through assign_null, which must
-- release a read/write expanded value properly rather than pfree its header
create or replace function case_array_loop(n int) returns text language uplpgsql as $$
declare a int[] := array[1,2]; r text; i int;
  for i in 1..n loop
    case a when array[1,2] then r := 'm'; else r := 'n'; end case;
  end loop;
  return r;
select case_array_loop(2000);
drop function case_text();
drop function case_bool();
drop function case_numeric();
drop function case_float8();
drop function case_date();
drop function case_array();
drop function case_int();
drop function case_bigint();
drop function case_else();
drop function case_multi();
drop function case_when_list();
drop function case_no_match();
drop function case_null_test();
drop function case_nested();
drop function case_body(int);
drop function case_array_loop(int);
Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

plpgsql_control.sql

Latest commit

History

plpgsql_control.sql

File metadata and controls
