1. -- Drop and recreate lyric type safely
  2. DO $$ BEGIN
  3.     IF EXISTS (SELECT 1 FROM pg_type WHERE typname = 'lyric') THEN
  4.         DROP TYPE lyric CASCADE;
  5.     END IF;
  6. END $$;
  7.  
  8. -- Create the lyric composite type with longer gift text
  9. CREATE TYPE lyric AS (
  10.   days VARCHAR(8),
  11.   gift VARCHAR(64)
  12. );
  13.  
  14. -- Create the function twelve_days that returns a string array
  15. CREATE OR REPLACE FUNCTION twelve_days(
  16.     IN pv_days VARCHAR(8)[],
  17.     IN pv_gifts LYRIC[]
  18. )
  19.     RETURNS TEXT[] AS
  20. $$
  21. DECLARE
  22.     lv_retval TEXT[] := ARRAY[]::TEXT[];
  23.     verse_line TEXT;
  24. BEGIN
  25.     FOR i IN 1..ARRAY_LENGTH(pv_days, 1) LOOP
  26.         -- Opening line for the day
  27.         lv_retval := ARRAY_APPEND(lv_retval, 'On the ' || pv_days[i] || ' day of Christmas, my true love sent to me');
  28.  
  29.         -- Append each gift from i down to 1
  30.         FOR j IN REVERSE 1..i LOOP
  31.             IF j = 1 THEN
  32.                 IF i = 1 THEN
  33.                     verse_line := pv_gifts[1].days || ' ' || pv_gifts[1].gift;
  34.                 ELSE
  35.                     verse_line := 'and ' || LOWER(pv_gifts[1].days || ' ' || pv_gifts[1].gift);
  36.                 END IF;
  37.             ELSE
  38.                 verse_line := pv_gifts[j].days || ' ' || pv_gifts[j].gift;
  39.             END IF;
  40.                 lv_retval := ARRAY_APPEND(lv_retval, verse_line);
  41.         END LOOP;
  42.  
  43.  
  44.     -- Add a blank line between verses
  45.         lv_retval := ARRAY_APPEND(lv_retval, '');
  46.     END LOOP;
  47.  
  48.     RETURN lv_retval;
  49. END;
  50. $$ LANGUAGE plpgsql;
  51.  
  52. -- Run a DO block to test the function
  53. DO $$
  54. DECLARE
  55.   lv_days  VARCHAR(8)[] := ARRAY['first','second','third','fourth','fifth','sixth','seventh','eighth','ninth','tenth','eleventh','twelfth'];
  56.   lv_gifts LYRIC[] := ARRAY[
  57.     ROW('','partridge in a pear tree')::lyric,
  58.     ROW('Two','turtle doves')::lyric,
  59.     ROW('Three','French hens')::lyric,
  60.     ROW('Four','calling birds')::lyric,
  61.     ROW('Five','golden rings')::lyric,
  62.     ROW('Six','geese a laying')::lyric,
  63.     ROW('Seven','swans a swimming')::lyric,
  64.     ROW('Eight','maids a milking')::lyric,
  65.     ROW('Nine','ladies dancing')::lyric,
  66.     ROW('Ten','lords a leaping')::lyric,
  67.     ROW('Eleven','pipers piping')::lyric,
  68.     ROW('Twelve','drummers drumming')::lyric
  69.   ];
  70.   lv_song  TEXT[];
  71.   verse_line TEXT;
  72. BEGIN
  73.   lv_song := twelve_days(lv_days, lv_gifts);
  74.  
  75.   FOREACH verse_line IN ARRAY lv_song LOOP
  76.     IF verse_line IS NOT NULL AND LENGTH(TRIM(verse_line)) > 0 THEN
  77.       RAISE NOTICE '%', verse_line;
  78.     ELSE
  79.       RAISE NOTICE '';
  80.     END IF;
  81.   END LOOP;
  82. END;
  83. $$;
  84.