diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile index 76ac247..14a4d83 100644 *** a/src/pl/plpgsql/src/Makefile --- b/src/pl/plpgsql/src/Makefile *************** DATA = plpgsql.control plpgsql--1.0.sql *** 26,32 **** REGRESS_OPTS = --dbname=$(PL_TESTDB) ! REGRESS = plpgsql_call all: all-lib --- 26,32 ---- REGRESS_OPTS = --dbname=$(PL_TESTDB) ! REGRESS = plpgsql_call plpgsql_control all: all-lib diff --git a/src/pl/plpgsql/src/expected/plpgsql_control.out b/src/pl/plpgsql/src/expected/plpgsql_control.out index ...b7089e3 . *** a/src/pl/plpgsql/src/expected/plpgsql_control.out --- b/src/pl/plpgsql/src/expected/plpgsql_control.out *************** *** 0 **** --- 1,833 ---- + -- + -- 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---'; + <> + loop + _i := _i - 1; + loop + raise notice '%', _i; + continue lbl when _i > 0; + exit lbl; + end loop; + end loop; + + raise notice '---3---'; + <> + 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---'; + <> + 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---'; + <> + for _i in 1..2 loop + raise notice 'outer %', _i; + <> + 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 + 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 + 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 $$ + <> + begin + <> + for i in 1 .. 10 loop + raise notice 'i = %', i; + exit flbl1; + end loop flbl1; + <> + 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 unlabelled block + LINE 5: end loop flbl1; + ^ + -- should fail: end label does not match start label + create function end_label3() returns void as $$ + <> + begin + <> + 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 $$ + <> + begin + for _i in 1 .. 10 loop + exit; + end loop outer_label; + end; + $$ language plpgsql; + ERROR: end label "outer_label" specified for unlabelled block + LINE 6: end loop outer_label; + ^ + -- unlabeled exit matches no blocks + do $$ + begin + for i in 1..10 loop + <> + 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 $$ + <> + begin + <> + begin + <> + 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 to a matching outer loop (other cases are covered elsewhere) + do $$ + begin + <> + for i in 1..10 loop + <> + for j in 1..10 loop + exit outerloop; + 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 + -- return out of a loop + create function return_from_loop() returns int language plpgsql as $$ + begin + for i in 1..10 loop + if i > 3 then + return i; + end if; + end loop; + return null; + end$$; + select return_from_loop(); + return_from_loop + ------------------ + 4 + (1 row) + + -- unlabeled exit does match a while loop + do $$ + begin + <> + while 1 > 0 loop + <> + while 1 > 0 loop + <> + 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 + <> + 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 + <> + while i < 2 loop + raise notice 'outermostwhile, i = %', i; + i := i + 1; + <> + while 1 > 0 loop + <> + 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) + + -- exit out of a foreach + do $$ + declare i int; j int; k int; + begin + <> + foreach i in array array[1,2,3] loop + <> + foreach j in array array[1,2,3] loop + <> + foreach k in array array[1,2,3] loop + exit; + raise notice 'should not get here'; + end loop; + raise notice 'should get here'; + exit outermostforeach; + 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 + -- continue out of a foreach + do $$ + declare i int; j int; k int; + begin + <> + foreach i in array array[1,2,3] loop + <> + foreach j in array array[1,2,3] loop + <> + foreach k in array array[1,2,3] loop + continue; + raise notice 'should not get here'; + end loop; + raise notice 'should get here, k = %', k; + continue outermostforeach; + raise notice 'should not get here, either'; + end loop; + raise notice 'nor here'; + end loop; + raise notice 'should get here, i = %, j = %, k = %', i, j, k; + end$$; + NOTICE: should get here, k = 3 + NOTICE: should get here, k = 3 + NOTICE: should get here, k = 3 + NOTICE: should get here, i = 3, j = 1, k = 3 + -- return out of a foreach + create function return_from_foreach() returns int language plpgsql as $$ + declare i int := 0; + begin + foreach i in array array[1,2,3] loop + if i > 1 then + return i; + end if; + end loop; + return null; + end$$; + select return_from_foreach(); + return_from_foreach + --------------------- + 2 + (1 row) + + -- exit out of a for-over-query + do $$ + declare i int; j int; k int; + begin + <> + for i in select generate_series(1,3) loop + <> + for j in select generate_series(1,3) loop + <> + for k in select generate_series(1,3) loop + exit; + raise notice 'should not get here'; + end loop; + raise notice 'should get here'; + exit outermostfor; + 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 + -- continue out of a for-over-query + do $$ + declare i int; j int; k int; + begin + <> + for i in select generate_series(1,3) loop + <> + for j in select generate_series(1,3) loop + <> + for k in select generate_series(1,3) loop + continue; + raise notice 'should not get here'; + end loop; + raise notice 'should get here, k = %', k; + continue outermostfor; + raise notice 'should not get here, either'; + end loop; + raise notice 'nor here'; + end loop; + raise notice 'should get here, i = %, j = %, k = %', i, j, k; + end$$; + NOTICE: should get here, k = 3 + NOTICE: should get here, k = 3 + NOTICE: should get here, k = 3 + NOTICE: should get here, i = 3, j = 1, k = 3 + -- return out of a for-over-query + create function return_from_for_query() returns int language plpgsql as $$ + declare i int := 0; + begin + for i in select generate_series(1,3) loop + if i > 1 then + return i; + end if; + end loop; + return null; + end$$; + select return_from_for_query(); + return_from_for_query + ----------------------- + 2 + (1 row) + + -- using list of scalars in fori and fore stmts + create function for_vect() returns void as $proc$ + <>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) + diff --git a/src/pl/plpgsql/src/sql/plpgsql_control.sql b/src/pl/plpgsql/src/sql/plpgsql_control.sql index ...2a9dfa9 . *** a/src/pl/plpgsql/src/sql/plpgsql_control.sql --- b/src/pl/plpgsql/src/sql/plpgsql_control.sql *************** *** 0 **** --- 1,620 ---- + -- + -- 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$$; + + -- 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$$; + + do $$ + begin + for i in 1..3 by -1 loop + raise notice '1..3 by -1: i = %', i; + end loop; + end$$; + + do $$ + begin + for i in reverse 1..3 by -1 loop + raise notice 'reverse 1..3 by -1: i = %', i; + end loop; + end$$; + + + -- 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---'; + <> + loop + _i := _i - 1; + loop + raise notice '%', _i; + continue lbl when _i > 0; + exit lbl; + end loop; + end loop; + + raise notice '---3---'; + <> + 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---'; + <> + 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---'; + <> + for _i in 1..2 loop + raise notice 'outer %', _i; + <> + 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(); + + -- should fail: CONTINUE is only legal inside a loop + create function continue_error1() returns void as $$ + begin + begin + continue; + end; + end; + $$ language plpgsql; + + -- should fail: unlabeled EXIT is only legal inside a loop + create function exit_error1() returns void as $$ + begin + begin + exit; + end; + end; + $$ language plpgsql; + + -- 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; + + -- 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; + + -- should fail: CONTINUE can't reference the label of a named block + create function continue_error3() returns void as $$ + begin + <> + begin + loop + continue begin_block1; + end loop; + end; + end; + $$ language plpgsql; + + -- On the other hand, EXIT *can* reference the label of a named block + create function exit_block1() returns void as $$ + begin + <> + begin + loop + exit begin_block1; + raise exception 'should not get here'; + end loop; + end; + end; + $$ language plpgsql; + + select exit_block1(); + + -- verbose end block and end loop + create function end_label1() returns void as $$ + <> + begin + <> + for i in 1 .. 10 loop + raise notice 'i = %', i; + exit flbl1; + end loop flbl1; + <> + for j in 1 .. 10 loop + raise notice 'j = %', j; + exit flbl2; + end loop; + end blbl; + $$ language plpgsql; + + select end_label1(); + + -- 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; + + -- should fail: end label does not match start label + create function end_label3() returns void as $$ + <> + begin + <> + for _i in 1 .. 10 loop + exit; + end loop outer_label; + end; + $$ language plpgsql; + + -- should fail: end label on a block without a start label + create function end_label4() returns void as $$ + <> + begin + for _i in 1 .. 10 loop + exit; + end loop outer_label; + end; + $$ language plpgsql; + + -- unlabeled exit matches no blocks + do $$ + begin + for i in 1..10 loop + <> + 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$$; + + -- check exit out of an unlabeled block to a labeled one + do $$ + <> + begin + <> + begin + <> + 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$$; + + -- check exit to a matching outer loop (other cases are covered elsewhere) + do $$ + begin + <> + for i in 1..10 loop + <> + for j in 1..10 loop + exit outerloop; + raise notice 'should not get here'; + end loop; + raise notice 'should not get here, either'; + end loop; + raise notice 'should get here'; + end$$; + + -- return out of a loop + create function return_from_loop() returns int language plpgsql as $$ + begin + for i in 1..10 loop + if i > 3 then + return i; + end if; + end loop; + return null; + end$$; + + select return_from_loop(); + + -- unlabeled exit does match a while loop + do $$ + begin + <> + while 1 > 0 loop + <> + while 1 > 0 loop + <> + 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$$; + + -- check exit out of an unlabeled while to a labeled one + do $$ + begin + <> + 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$$; + + -- continue to an outer while + do $$ + declare i int := 0; + begin + <> + while i < 2 loop + raise notice 'outermostwhile, i = %', i; + i := i + 1; + <> + while 1 > 0 loop + <> + 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$$; + + -- 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(); + + -- exit out of a foreach + do $$ + declare i int; j int; k int; + begin + <> + foreach i in array array[1,2,3] loop + <> + foreach j in array array[1,2,3] loop + <> + foreach k in array array[1,2,3] loop + exit; + raise notice 'should not get here'; + end loop; + raise notice 'should get here'; + exit outermostforeach; + raise notice 'should not get here, either'; + end loop; + raise notice 'nor here'; + end loop; + raise notice 'should get here, too'; + end$$; + + -- continue out of a foreach + do $$ + declare i int; j int; k int; + begin + <> + foreach i in array array[1,2,3] loop + <> + foreach j in array array[1,2,3] loop + <> + foreach k in array array[1,2,3] loop + continue; + raise notice 'should not get here'; + end loop; + raise notice 'should get here, k = %', k; + continue outermostforeach; + raise notice 'should not get here, either'; + end loop; + raise notice 'nor here'; + end loop; + raise notice 'should get here, i = %, j = %, k = %', i, j, k; + end$$; + + -- return out of a foreach + create function return_from_foreach() returns int language plpgsql as $$ + declare i int := 0; + begin + foreach i in array array[1,2,3] loop + if i > 1 then + return i; + end if; + end loop; + return null; + end$$; + + select return_from_foreach(); + + -- exit out of a for-over-query + do $$ + declare i int; j int; k int; + begin + <> + for i in select generate_series(1,3) loop + <> + for j in select generate_series(1,3) loop + <> + for k in select generate_series(1,3) loop + exit; + raise notice 'should not get here'; + end loop; + raise notice 'should get here'; + exit outermostfor; + raise notice 'should not get here, either'; + end loop; + raise notice 'nor here'; + end loop; + raise notice 'should get here, too'; + end$$; + + -- continue out of a for-over-query + do $$ + declare i int; j int; k int; + begin + <> + for i in select generate_series(1,3) loop + <> + for j in select generate_series(1,3) loop + <> + for k in select generate_series(1,3) loop + continue; + raise notice 'should not get here'; + end loop; + raise notice 'should get here, k = %', k; + continue outermostfor; + raise notice 'should not get here, either'; + end loop; + raise notice 'nor here'; + end loop; + raise notice 'should get here, i = %, j = %, k = %', i, j, k; + end$$; + + -- return out of a for-over-query + create function return_from_for_query() returns int language plpgsql as $$ + declare i int := 0; + begin + for i in select generate_series(1,3) loop + if i > 1 then + return i; + end if; + end loop; + return null; + end$$; + + select return_from_for_query(); + + -- using list of scalars in fori and fore stmts + create function for_vect() returns void as $proc$ + <>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(); + + -- 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); + 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 $$ + 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(); + + -- 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); + select case_test(2); + select case_test(12); + select case_test(13); diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index a2df411..4783807 100644 *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** NOTICE: {10,20,30}; 20; xyz; xyzabc; (1 *** 2706,3044 **** (1 row) drop function raise_exprs(); - -- 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---'; - <> - loop - _i := _i - 1; - loop - raise notice '%', _i; - continue lbl when _i > 0; - exit lbl; - end loop; - end loop; - - raise notice '---3---'; - <> - 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---'; - for _i in 1..3 loop - raise notice '%', _i; - continue when _i = 3; - 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; - 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: 2 - NOTICE: 3 - NOTICE: ---8--- - NOTICE: 1 - NOTICE: 2 - NOTICE: 3 - NOTICE: ---9--- - NOTICE: 10 - NOTICE: ---10--- - NOTICE: 10 - continue_test1 - ---------------- - - (1 row) - - drop function continue_test1(); - drop table conttesttbl; - -- 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 - 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 - loop - exit begin_block1; - raise exception 'should not get here'; - end loop; - end; - end; - $$ language plpgsql; - select exit_block1(); - exit_block1 - ------------- - - (1 row) - - drop function exit_block1(); - -- verbose end block and end loop - create function end_label1() returns void as $$ - <> - begin - <> - for _i in 1 .. 10 loop - exit flbl1; - end loop flbl1; - <> - for _i in 1 .. 10 loop - exit flbl2; - end loop; - end blbl; - $$ language plpgsql; - select end_label1(); - end_label1 - ------------ - - (1 row) - - drop function end_label1(); - -- 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 unlabelled block - LINE 5: end loop flbl1; - ^ - -- should fail: end label does not match start label - create function end_label3() returns void as $$ - <> - begin - <> - 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 $$ - <> - begin - for _i in 1 .. 10 loop - exit; - end loop outer_label; - end; - $$ language plpgsql; - ERROR: end label "outer_label" specified for unlabelled block - LINE 6: end loop outer_label; - ^ - -- using list of scalars in fori and fore stmts - create function for_vect() returns void as $proc$ - <>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) - -- regression test: verify that multiple uses of same plpgsql datum within -- a SQL command all get mapped to the same $n parameter. The return value -- of the SELECT is not important, we only care that it doesn't fail with --- 2706,2711 ---- *************** NOTICE: column >>some column name<<, co *** 4368,4503 **** (1 row) drop function stacked_diagnostics_test(); - -- test 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) - - drop function catch(); - drop function case_test(bigint); -- test variadic functions create or replace function vari(variadic int[]) returns void as $$ --- 4035,4040 ---- *************** create function consumes_rw_array(int[]) *** 5409,5414 **** --- 4946,4957 ---- language plpgsql as $$ begin return $1[1]; end; $$ stable; + select consumes_rw_array(returns_rw_array(42)); + consumes_rw_array + ------------------- + 42 + (1 row) + -- bug #14174 explain (verbose, costs off) select i, a from *************** select consumes_rw_array(a), a from *** 5465,5470 **** --- 5008,5020 ---- 2 | {2,2} (2 rows) + do $$ + declare a int[] := array[1,2]; + begin + a := a || 3; + raise notice 'a = %', a; + end$$; + NOTICE: a = {1,2,3} -- -- Test access to call stack -- diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 02c8913..768270d 100644 *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** end;$$ language plpgsql; *** 2285,2525 **** select raise_exprs(); drop function raise_exprs(); - -- 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---'; - <> - loop - _i := _i - 1; - loop - raise notice '%', _i; - continue lbl when _i > 0; - exit lbl; - end loop; - end loop; - - raise notice '---3---'; - <> - 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---'; - for _i in 1..3 loop - raise notice '%', _i; - continue when _i = 3; - 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; - end; $$ language plpgsql; - - select continue_test1(); - - drop function continue_test1(); - drop table conttesttbl; - - -- should fail: CONTINUE is only legal inside a loop - create function continue_error1() returns void as $$ - begin - begin - continue; - end; - end; - $$ language plpgsql; - - -- should fail: unlabeled EXIT is only legal inside a loop - create function exit_error1() returns void as $$ - begin - begin - exit; - end; - end; - $$ language plpgsql; - - -- 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; - - -- 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; - - -- should fail: CONTINUE can't reference the label of a named block - create function continue_error3() returns void as $$ - begin - <> - begin - loop - continue begin_block1; - end loop; - end; - end; - $$ language plpgsql; - - -- On the other hand, EXIT *can* reference the label of a named block - create function exit_block1() returns void as $$ - begin - <> - begin - loop - exit begin_block1; - raise exception 'should not get here'; - end loop; - end; - end; - $$ language plpgsql; - - select exit_block1(); - drop function exit_block1(); - - -- verbose end block and end loop - create function end_label1() returns void as $$ - <> - begin - <> - for _i in 1 .. 10 loop - exit flbl1; - end loop flbl1; - <> - for _i in 1 .. 10 loop - exit flbl2; - end loop; - end blbl; - $$ language plpgsql; - - select end_label1(); - drop function end_label1(); - - -- 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; - - -- should fail: end label does not match start label - create function end_label3() returns void as $$ - <> - begin - <> - for _i in 1 .. 10 loop - exit; - end loop outer_label; - end; - $$ language plpgsql; - - -- should fail: end label on a block without a start label - create function end_label4() returns void as $$ - <> - begin - for _i in 1 .. 10 loop - exit; - end loop outer_label; - end; - $$ language plpgsql; - - -- using list of scalars in fori and fore stmts - create function for_vect() returns void as $proc$ - <>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(); - -- regression test: verify that multiple uses of same plpgsql datum within -- a SQL command all get mapped to the same $n parameter. The return value -- of the SELECT is not important, we only care that it doesn't fail with --- 2285,2290 ---- *************** select stacked_diagnostics_test(); *** 3580,3651 **** drop function stacked_diagnostics_test(); - -- test 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); - 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 $$ - 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(); - - -- 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); - select case_test(2); - select case_test(12); - select case_test(13); - - drop function catch(); - drop function case_test(bigint); - -- test variadic functions create or replace function vari(variadic int[]) --- 3345,3350 ---- *************** language plpgsql as $$ *** 4278,4283 **** --- 3977,3984 ---- begin return $1[1]; end; $$ stable; + select consumes_rw_array(returns_rw_array(42)); + -- bug #14174 explain (verbose, costs off) select i, a from *************** select consumes_rw_array(a), a from *** 4300,4305 **** --- 4001,4013 ---- select consumes_rw_array(a), a from (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); + do $$ + declare a int[] := array[1,2]; + begin + a := a || 3; + raise notice 'a = %', a; + end$$; + -- -- Test access to call stack