1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
-- Perl Weekly Challenge 160
CREATE SCHEMA IF NOT EXISTS pwc160;
/*
testdb=> select pwc160.four_is_magic_plpgsql( 7 );
four_is_magic_plpgsql
-----------------------
Seven is 5
Five is 4
Four is magic
*/
CREATE TABLE IF NOT EXISTS pwc160.words
(
pk int generated always as identity
, n int not null
, w text not null
, stop boolean default false
, wlen int generated always as ( length( w ) ) stored
, primary key( pk )
, unique( n )
);
TRUNCATE pwc160.words;
INSERT INTO pwc160.words( n, w )
VALUES
( 1, 'One' )
, ( 2, 'Two' )
, ( 3, 'Three' )
, ( 4, 'Four' )
, ( 5, 'Five' )
, ( 6, 'Six' )
, ( 7, 'Seven' )
, ( 8, 'Eight' )
, ( 9, 'Nine' );
UPDATE pwc160.words
SET stop = true
WHERE n = 4;
CREATE OR REPLACE FUNCTION
pwc160.four_is_magic_plpgsql( needle int )
RETURNS SETOF text
AS $CODE$
DECLARE
current_row pwc160.words%rowtype;
BEGIN
SELECT *
INTO current_row
FROM pwc160.words
WHERE n = needle;
WHILE NOT current_row.stop LOOP
RETURN NEXT current_row.w || ' is ' || current_row.wlen;
SELECT *
INTO current_row
FROM pwc160.words
WHERE n = current_row.wlen;
END LOOP;
RETURN NEXT current_row.w || ' is magic';
RETURN;
END
$CODE$
LANGUAGE plpgsql;
|
