Two case studies: Using various kinds of "loop" statement, the "exit" statement, and the "continue" statement
The first case study demonstrates the use of these statements:
- the foreach loop statement to iterate over all the elements of an array
- the infinite loop statement together with the exit statement to extract words from lines of text
- the cursor for loop statement to iterate over the results of a table function
- the integer range for loop statement to iterate over the elements of an array that lie between a specified range of index values
The second case study demonstrates the use of these statements:
- the foreach loop statement to iterate over all the elements of an array
- the integer range for loop statement to iterate over the characters in a string
- the exit statement to terminate the iteration of both a foreach loop statement and an integer range for loop statement when enough results have been found
- the continue statement to abandon the current iteration of a loop and to start the next iteration of the same loop when a test shows that the current result fails to satisfy a requirement
- the continue statement with a label to abandon the current iteration of an inner loop and to start the next iteration of the outer loop when a test shows that the inner loop has already met the processing requirement for the outer loop's current element.
Common setup
Both studies use an array of lines of text. Set it up thus:
\c :db :u
drop schema if exists s cascade;
create schema s;
create function s.dickens_lines()
returns text[]
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
lines constant text[] not null := array[
' The carrier’s horse was the laziest horse in the world, I should hope, and shuffled along, ',
' with his head down, as if he liked to keep people waiting to whom the packages were ',
' directed. I fancied, indeed, that he sometimes chuckled audibly over this reflection, ',
' but the carrier said he was only troubled with a cough. The carrier had a way of keeping ',
' his head down, like his horse, and of drooping sleepily forward as he drove, with one of ',
' his arms on each of his knees. I say “drove”, but it struck me that the cart would have ',
' gone to Yarmouth quite as well without him, for the horse did all that; and as to ',
' conversation, he had no idea of it but whistling. Peggotty had a basket of refreshments ',
' on her knee, which would have lasted us out handsomely, if we had been going to London by ',
' the same conveyance. We ate a good deal, and slept a good deal. Peggotty always went to ',
' sleep with her chin upon the handle of the basket, her hold of which never relaxed; and I ',
' could not have believed unless I had heard her do it, that one defenceless woman could ',
' have snored so much. We made so many deviations up and down lanes, and were such a long ',
' time delivering a bedstead at a public house, and calling at other places, that I was ',
' quite tired, and very glad, when we saw Yarmouth. It looked rather spongy and soppy, I ',
' thought, as I carried my eye over the great dull waste that lay across the river; and I ',
' could not help wondering, if the world were really as round as my geography book said, how ',
' any part of it came to be so flat. But I reflected that Yarmouth might be situated at one ',
' of the poles; which would account for it. As we drew a little nearer, and saw the whole ',
' adjacent prospect lying a straight low line under the sky, I hinted to Peggotty that a ',
' mound or so might have improved it; and also that if the land had been a little more ',
' separated from the sea, and the town and the tide had not been quite so much mixed up, ',
' like toast and water, it would have been nicer. But Peggotty said, with greater emphasis ',
' than usual, that we must take things as we found them, and that, for her part, she was ',
' proud to call herself a Yarmouth Bloater. When we got into the street (which was strange ',
' enough to me) and smelt the fish, and pitch, and oakum, and tar, and saw the sailors ',
' walking about, and the carts jingling up and down over the stones, I felt that I had done ',
' so busy a place an injustice; and said as much to Peggotty, who heard my expressions of ',
' delight with great complacency, and told me it was well known (I suppose to those who had ',
' the good fortune to be born Bloaters) that Yarmouth was, upon the whole, the finest place ',
' in the universe. He was waiting for us, in fact, at the public house; and asked me how I ',
' found myself, like an old acquaintance. I did not feel, at first, that I knew him as well ',
' as he knew me, because he had never come to our house since the night I was born, and ',
' naturally he had the advantage of me. But our intimacy was much advanced by his taking me ',
' on his back to carry me home. He was, now, a huge, strong fellow of six feet high, broad ',
' in proportion, and round-shouldered; but with a simpering boy’s face and curly light hair ',
' that gave him quite a sheepish look. He was dressed in a canvas jacket, and a pair of such ',
' very stiff trousers that they would have stood quite as well alone, without any legs in ',
' them. And you couldn’t so properly have said he wore a hat, as that he was covered in a ',
' top, like an old building, with something pitchy. Ham carrying me on his back and a small '
];
begin
return lines;
end;
$body$;
It isn't necessary to clutter the text literal with $$ quoting because the text itself contains no straight single quotes but, rather, uses a proper (curly) apostrophe where this is needed.
Case study #1: extract the words from an array of text lines
Problem statement
The heart of the solution is the code that extracts the words from a single line of text. This relies on just the infinite loop statement together with the exit when statement. But extending the problem statement to require extracting the words from an array of text lines brings opportunities to demonstrate other kinds of loop statement.
The code
First create a function to find the first word in a line of text and to return that word together with what remains when it is removed from the text.
create type s.first_word_and_remainder as (word text, remainder text);
create function s.split_string(str in text)
returns s.first_word_and_remainder
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
result s.first_word_and_remainder;
begin
result.word := (regexp_match(str, '[a-zA-Z’]+'))[1];
result.remainder := regexp_replace(str, result.word, '');
return result;
end;
$body$;
The design of this function isn't of interest for the present case study's pedagogy. But it's of note that the implementation takes just two lines. Because the words in the text used here happen to use only latin letters and curly apostrophe, the regular expression that defines a word is very short. It would need to be more elaborate it had to accommodate European languages that use letters like these: Å Ø å æ ø. Test it like this.
create function s.split_string_test_results(str_in in text)
returns text
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
str text not null := str_in;
r text not null := '';
split_string s.first_word_and_remainder;
begin
loop
split_string := s.split_string(str);
exit when split_string.word is null;
r := r||split_string.word||' | ';
str := split_string.remainder;
end loop;
return r;
end;
$body$;
select s.split_string_test_results(' Mary’s most frequent typo: confusing “its” and “it’s”. ');
This is the result:
Mary’s | most | frequent | typo | confusing | its | and | it’s |
The test demonstrates the main algorithm for this case study. It uses the infinite loop statement like this:
set up for the first iteration
repeat
do some processing
exit when finished
note this iteration's result
set up for the next iteration
end repeat
It's a common pattern—and it's sometimes called the "loop-and-a-half" in generic computer science writings.
As an exercise, try to rewrite it using a while loop statement. You'll find that the code is more elaborate—and is therefore harder to understand and more prone to bugs.
The solution to this case study's actual problem is just a trivial extension to the function s.split_string_test_results(). First, create a helper function so that the interesting code can be separated from the display of the results. It shows just the first ten words and the last ten words that have been extracted. Each set of ten words is split over two lines.
create function s.display(results in text[])
returns table(x text)
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
lb int not null := 0;
begin
for j in 1..4 loop
case j
when 1 then lb := 1;
when 2 then lb := 6;
when 3 then lb := cardinality(results) - 9; x := ''; return next;
when 4 then lb := cardinality(results) - 4;
end case;
x := '';
for j in lb..(lb + 4) loop
x := x||rpad(results[j], 15);
end loop;
return next;
end loop;
end;
$body$;
It turns out the using the function s.tokenized_lines() demonstrates another interesting use of a loop statement. Look for this:
for z in (select x from s.display(results)) loop
return next;
end loop;
This is the standard pattern for consuming the output of a table function whose purpose is to display some results within a second table function whose purpose is to display other results.
Create the function s.tokenized_lines() to implement the case study's solution:
create function s.tokenized_lines()
returns table(z text)
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
lines constant text[] not null := s.dickens_lines();
line text not null := '';
results text[] not null := '{}';
split_line s.first_word_and_remainder;
begin
<<lines>>
foreach line in array lines loop
<<words>>
loop
split_line := s.split_string(line);
exit words when split_line.word is null;
results := results||split_line.word;
line := split_line.remainder;
end loop words;
end loop lines;
for z in (select x from s.display(results)) loop
return next;
end loop;
end;
$body$;
Execute it:
select s.tokenized_lines();
This is the result:
The carrier’s horse was the
laziest horse in the world
pitchy Ham carrying me on
his back and a small
Case study #2: compose a string of a specified number of vowels from each text line in an array until a specified number of such vowel strings have been composed
This case study doesn't use any kinds of loop statement that case study #1 doesn't use. But it does use the continue statement. Especially, it shows its use with a label to abort the current iteration of an inner loop and then to start the next iteration of an outer loop. You might like to try to rewrite the code without using the continue statement. You'll need to use an if statement or a case statement together with a dedicated guard variable. The result will be cluttered code that is also less efficient than the code that's shown here.
Problem statement
Suppose that you have a one-dimensional text array where each successive element holds the next line from, say, a novel. The goal is to implement this:
- Make the first line current.
- Traverse the characters in the current line from the first one to (up to) the last one.
- Inspect the current character. If it's a vowel, then note it. Otherwise skip it.
- Abandon the current line:
- either if it contains Peggotty
- or when the same vowel is noted twice in succession
- or when the specified number of distinct vowels (say seven) have been noted
- Record the vowel string only if its length is the specified number of distinct vowels.
- Make the next line current. Repeat until the specified number of vowel strings (say ten) of the specified length have been recorded.
- Display the recorded vowel strings.
- Finish.
Never mind if the goal seems artificial. It's always instructive to solve a well-specified problem when doing so requires some creative thinking, design, implementation, and testing.
The code
First, create a trivial helper function to test if a single character is a Latin alphabet vowel:
create function s.vowel(c in text)
returns boolean
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
begin
assert length(c) = 1;
return c in ('a', 'e', 'i', 'o', 'u');
end;
$body$;
Now create the table function s.f() to find the vowel strings and display them.
create function s.vowels_from_lines(no_of_vowels in int, no_of_results in int)
returns table(z text)
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
lines constant text[] not null := s.dickens_lines();
line text not null := '';
prev text not null := '';
c text not null := '';
vowels text not null := '';
results text[] not null := '{}';
r int not null := 0;
begin
<<lines>>
foreach line in array lines loop -- Inspect up to every line.
continue lines when
line like '%Peggotty%'; -- Don't process any "line" that
-- contains "Peggotty".
c := '';
vowels := '';
prev := '';
<<characters>>
for pos in 1..length(line) loop -- Inspect up to every character in each line.
c := substring(line from pos for 1);
if s.vowel(c)
then vowels := vowels||c;
continue lines when
c = prev; -- Abandon this line when two successively
-- found vowels are the same.
prev := c;
end if;
exit characters when
length(vowels) >= no_of_vowels; -- Don't want "vowels" values longer
-- than "no_of_vowels".
end loop characters;
if length(vowels) = no_of_vowels then
r := r + 1;
results[r] := vowels;
end if;
exit lines when
r >= no_of_results; -- Finish when have enough results.
end loop lines;
foreach z in array results loop
return next;
end loop;
end;
$body$;
Test it like this:
select s.vowels_from_lines(7, 10);
This is the result:
eaieoea
ueaieai
ieaoiei
iaoeaoi
aeoeoue
uieieae
ouoeoei
oeoeiou
eouoeae
aiaouae
Notice these uses of the continue statement:
-- At top level in the outer "lines" loop.
continue lines when line like '%Peggotty%';
and:
-- Nested inside the inner "characters" loop within the outer "lines" loop.
continue lines when c = prev;
to abandon processing the current line and to move to the next one in the outer lines loop.
Notice these uses of the exit statement:
exit characters when length(vowels) >= no_of_vowels;
to exit the inner characters loop when the vowels string is long enough. And:
exit lines when r >= no_of_results;
to exit the lines loop when enough results have been recorded.