path: root/src/pl/plpgsql/src/expected/plpgsql_control.out

--
-- Tests for PL/pgSQL control structures
--
-- integer FOR loop
do $$
begin
  -- 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;
end$$;
NOTICE:  1..3: i = 1
NOTICE:  1..3: i = 2
NOTICE:  1..3: i = 3
NOTICE:  1..10 by 3: i = 1
NOTICE:  1..10 by 3: i = 4
NOTICE:  1..10 by 3: i = 7
NOTICE:  1..10 by 3: i = 10
NOTICE:  1..11 by 3: i = 1
NOTICE:  1..11 by 3: i = 4
NOTICE:  1..11 by 3: i = 7
NOTICE:  1..11 by 3: i = 10
NOTICE:  reverse 10..0 by 3: i = 10
NOTICE:  reverse 10..0 by 3: i = 7
NOTICE:  reverse 10..0 by 3: i = 4
NOTICE:  reverse 10..0 by 3: i = 1
NOTICE:  2147483620..2147483647 by 10: i = 2147483620
NOTICE:  2147483620..2147483647 by 10: i = 2147483630
NOTICE:  2147483620..2147483647 by 10: i = 2147483640
NOTICE:  reverse -2147483620..-2147483647 by 10: i = -2147483620
NOTICE:  reverse -2147483620..-2147483647 by 10: i = -2147483630
NOTICE:  reverse -2147483620..-2147483647 by 10: i = -2147483640
-- BY can't be zero or negative
do $$
begin
  for i in 1..3 by 0 loop
    raise notice '1..3 by 0: i = %', i;
  end loop;
end$$;
ERROR:  BY value of FOR loop must be greater than zero
CONTEXT:  PL/pgSQL function inline_code_block line 3 at FOR with integer loop variable
do $$
begin
  for i in 1..3 by -1 loop
    raise notice '1..3 by -1: i = %', i;
  end loop;
end$$;
ERROR:  BY value of FOR loop must be greater than zero
CONTEXT:  PL/pgSQL function inline_code_block line 3 at FOR with integer loop variable
do $$
begin
  for i in reverse 1..3 by -1 loop
    raise notice 'reverse 1..3 by -1: i = %', i;
  end loop;
end$$;
ERROR:  BY value of FOR loop must be greater than zero
CONTEXT:  PL/pgSQL function inline_code_block line 3 at FOR with integer loop variable
-- 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;
begin
  raise notice '---1---';
  loop
    _i := _i + 1;
    raise notice '%', _i;
    continue when _i < 10;
    exit;
  end loop;

  raise notice '---2---';
  <<lbl>>
  loop
    _i := _i - 1;
    loop
      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
    begin
      -- applies to outer loop, not the nested begin block
      continue when _i < 5;
      raise notice '%', _i;
    end;
  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---';
  _i := 1;
  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 plpgsql;
select continue_test1();
NOTICE:  ---1---
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  5
NOTICE:  6
NOTICE:  7
NOTICE:  8
NOTICE:  9
NOTICE:  10
NOTICE:  ---2---
NOTICE:  9
NOTICE:  8
NOTICE:  7
NOTICE:  6
NOTICE:  5
NOTICE:  4
NOTICE:  3
NOTICE:  2
NOTICE:  1
NOTICE:  0
NOTICE:  ---3---
NOTICE:  1
NOTICE:  3
NOTICE:  5
NOTICE:  7
NOTICE:  9
NOTICE:  ---4---
NOTICE:  5
NOTICE:  6
NOTICE:  7
NOTICE:  8
NOTICE:  9
NOTICE:  10
NOTICE:  ---5---
NOTICE:  30
NOTICE:  40
NOTICE:  ---6---
NOTICE:  30
NOTICE:  40
NOTICE:  ---7---
NOTICE:  1
NOTICE:  3
NOTICE:  ---8---
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  ---9---
NOTICE:  10
NOTICE:  ---10---
NOTICE:  10
NOTICE:  ---11---
NOTICE:  outer 1
NOTICE:  inner 1
NOTICE:  outer 2
NOTICE:  inner 1
 continue_test1 
----------------
 
(1 row)

-- should fail: CONTINUE is only legal inside a loop
create function continue_error1() returns void as $$
begin
    begin
        continue;
    end;
end;
$$ language plpgsql;
ERROR:  CONTINUE cannot be used outside a loop
LINE 4:         continue;
                ^
-- should fail: unlabeled EXIT is only legal inside a loop
create function exit_error1() returns void as $$
begin
    begin
        exit;
    end;
end;
$$ language plpgsql;
ERROR:  EXIT cannot be used outside a loop, unless it has a label
LINE 4:         exit;
                ^
-- should fail: no such label
create function continue_error2() returns void as $$
begin
    begin
        loop
            continue no_such_label;
        end loop;
    end;
end;
$$ language plpgsql;
ERROR:  there is no label "no_such_label" attached to any block or loop enclosing this statement
LINE 5:             continue no_such_label;
                             ^
-- should fail: no such label
create function exit_error2() returns void as $$
begin
    begin
        loop
            exit no_such_label;
        end loop;
    end;
end;
$$ language plpgsql;
ERROR:  there is no label "no_such_label" attached to any block or loop enclosing this statement
LINE 5:             exit no_such_label;
                         ^
-- should fail: CONTINUE can't reference the label of a named block
create function continue_error3() returns void as $$
begin
    <<begin_block1>>
    begin
        loop
            continue begin_block1;
        end loop;
    end;
end;
$$ language plpgsql;
ERROR:  block label "begin_block1" cannot be used in CONTINUE
LINE 6:             continue begin_block1;
                             ^
-- On the other hand, EXIT *can* reference the label of a named block
create function exit_block1() returns void as $$
begin
    <<begin_block1>>
    begin
        loop
            exit begin_block1;
            raise exception 'should not get here';
        end loop;
    end;
end;
$$ language plpgsql;
select exit_block1();
 exit_block1 
-------------
 
(1 row)

-- verbose end block and end loop
create function end_label1() returns void as $$
<<blbl>>
begin
  <<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;
end blbl;
$$ language plpgsql;
select end_label1();
NOTICE:  i = 1
NOTICE:  j = 1
 end_label1 
------------
 
(1 row)

-- should fail: undefined end label
create function end_label2() returns void as $$
begin
  for _i in 1 .. 10 loop
    exit;
  end loop flbl1;
end;
$$ language plpgsql;
ERROR:  end label "flbl1" specified for unlabeled block
LINE 5:   end loop flbl1;
                   ^
-- should fail: end label does not match start label
create function end_label3() returns void as $$
<<outer_label>>
begin
  <<inner_label>>
  for _i in 1 .. 10 loop
    exit;
  end loop outer_label;
end;
$$ language plpgsql;
ERROR:  end label "outer_label" differs from block's label "inner_label"
LINE 7:   end loop outer_label;
                   ^
-- should fail: end label on a block without a start label
create function end_label4() returns void as $$
<<outer_label>>
begin
  for _i in 1 .. 10 loop
    exit;
  end loop outer_label;
end;
$$ language plpgsql;
ERROR:  end label "outer_label" specified for unlabeled block
LINE 6:   end loop outer_label;
                   ^
-- unlabeled exit matches no blocks
do $$
begin
for i in 1..10 loop
  <<innerblock>>
  begin
    begin  -- unlabeled block
      exit;
      raise notice 'should not get here';
    end;
    raise notice 'should not get here, either';
  end;
  raise notice 'nor here';
end loop;
raise notice 'should get here';
end$$;
NOTICE:  should get here
-- check exit out of an unlabeled block to a labeled one
do $$
<<outerblock>>
begin
  <<innerblock>>
  begin
    <<moreinnerblock>>
    begin
      begin  -- unlabeled block
        exit innerblock;
        raise notice 'should not get here';
      end;
      raise notice 'should not get here, either';
    end;
    raise notice 'nor here';
  end;
  raise notice 'should get here';
end$$;
NOTICE:  should get here
-- check exit out of outermost block
do $$
<<outerblock>>
begin
  <<innerblock>>
  begin
    exit outerblock;
    raise notice 'should not get here';
  end;
  raise notice 'should not get here, either';
end$$;
-- unlabeled exit does match a while loop
do $$
begin
  <<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';
end$$;
NOTICE:  should get here
NOTICE:  should get here, too
-- check exit out of an unlabeled while to a labeled one
do $$
begin
  <<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';
end$$;
NOTICE:  should get here
-- continue to an outer while
do $$
declare i int := 0;
begin
  <<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;
end$$;
NOTICE:  outermostwhile, i = 0
NOTICE:  outermostwhile, i = 1
NOTICE:  out of outermostwhile, i = 2
-- return out of a while
create function return_from_while() returns int language plpgsql as $$
declare i int := 0;
begin
  while i < 10 loop
    if i > 2 then
      return i;
    end if;
    i := i + 1;
  end loop;
  return null;
end$$;
select return_from_while();
 return_from_while 
-------------------
                 3
(1 row)

-- 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;
begin
  -- fori
  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;
end;
$proc$ language plpgsql;
select for_vect();
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  1 BB CC
NOTICE:  2 BB CC
NOTICE:  3 BB CC
NOTICE:  4 BB CC
NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
NOTICE:  1 BB CC
NOTICE:  2 BB CC
NOTICE:  3 BB CC
NOTICE:  4 BB CC
NOTICE:  1 bb cc
NOTICE:  2 bb cc
NOTICE:  3 bb cc
NOTICE:  4 bb cc
 for_vect 
----------
 
(1 row)

-- CASE statement
create or replace function case_test(bigint) returns text as $$
declare a int = 10;
        b int = 1;
begin
  case $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;
end;
$$ language plpgsql immutable;
select case_test(1);
 case_test 
-----------
 one
(1 row)

select case_test(2);
 case_test 
-----------
 two
(1 row)

select case_test(3);
      case_test       
----------------------
 three, four or eight
(1 row)

select case_test(4);
      case_test       
----------------------
 three, four or eight
(1 row)

select case_test(5); -- fails
ERROR:  case not found
HINT:  CASE statement is missing ELSE part.
CONTEXT:  PL/pgSQL function case_test(bigint) line 5 at CASE
select case_test(8);
      case_test       
----------------------
 three, four or eight
(1 row)

select case_test(10);
 case_test 
-----------
 ten
(1 row)

select case_test(11);
   case_test    
----------------
 eleven, twelve
(1 row)

select case_test(12);
   case_test    
----------------
 eleven, twelve
(1 row)

select case_test(13); -- fails
ERROR:  case not found
HINT:  CASE statement is missing ELSE part.
CONTEXT:  PL/pgSQL function case_test(bigint) line 5 at CASE
create or replace function catch() returns void as $$
begin
  raise notice '%', case_test(6);
exception
  when case_not_found then
    raise notice 'caught case_not_found % %', SQLSTATE, SQLERRM;
end
$$ language plpgsql;
select catch();
NOTICE:  caught case_not_found 20000 case not found
 catch 
-------
 
(1 row)

-- test the searched variant too, as well as ELSE
create or replace function case_test(bigint) returns text as $$
declare a int = 10;
begin
  case
    when $1 = 1 then
      return 'one';
    when $1 = a + 2 then
      return 'twelve';
    else
      return 'other';
  end case;
end;
$$ language plpgsql immutable;
select case_test(1);
 case_test 
-----------
 one
(1 row)

select case_test(2);
 case_test 
-----------
 other
(1 row)

select case_test(12);
 case_test 
-----------
 twelve
(1 row)

select case_test(13);
 case_test 
-----------
 other
(1 row)

-- test line comment between WHEN and THEN
create or replace function case_comment(int) returns text as $$
begin
  case $1
    when 1 -- comment before THEN
      then return 'one';
    else
      return 'other';
  end case;
end;
$$ language plpgsql immutable;
select case_comment(1);
 case_comment 
--------------
 one
(1 row)