uplpgsql/test/plpgsql/sql/plpgsql_control.sql at fix/math-include · jdatcmd/uplpgsql

556 lines (500 loc) · 12.3 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();
-- 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();
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
