W05 - On the 12 days of Christmas

-- Drop and recreate lyric type safely
DO $$ BEGIN
    IF EXISTS (SELECT 1 FROM pg_type WHERE typname = 'lyric') THEN
        DROP TYPE lyric CASCADE;
    END IF;
END $$;

-- Create the lyric composite type with longer gift text
CREATE TYPE lyric AS (
  days VARCHAR(8),
  gift VARCHAR(64)
);

-- Create the function twelve_days that returns a string array
CREATE OR REPLACE FUNCTION twelve_days(
    IN pv_days VARCHAR(8)[],
    IN pv_gifts LYRIC[]
)
    RETURNS TEXT[] AS
$$
DECLARE
    lv_retval TEXT[] := ARRAY[]::TEXT[];
    verse_line TEXT;
BEGIN
    FOR i IN 1..ARRAY_LENGTH(pv_days, 1) LOOP
        -- Opening line for the day
        lv_retval := ARRAY_APPEND(lv_retval, 'On the ' || pv_days[i] || ' day of Christmas, my true love sent to me');

        -- Append each gift from i down to 1
        FOR j IN REVERSE 1..i LOOP
            IF j = 1 THEN
                IF i = 1 THEN
                    verse_line := pv_gifts[1].days || ' ' || pv_gifts[1].gift;
                ELSE
                    verse_line := 'and ' || LOWER(pv_gifts[1].days || ' ' || pv_gifts[1].gift);
                END IF;
            ELSE
                verse_line := pv_gifts[j].days || ' ' || pv_gifts[j].gift;
            END IF;
                lv_retval := ARRAY_APPEND(lv_retval, verse_line);
        END LOOP;


    -- Add a blank line between verses
        lv_retval := ARRAY_APPEND(lv_retval, '');
    END LOOP;

    RETURN lv_retval;
END;
$$ LANGUAGE plpgsql;

-- Run a DO block to test the function
DO $$
DECLARE
  lv_days  VARCHAR(8)[] := ARRAY['first','second','third','fourth','fifth','sixth','seventh','eighth','ninth','tenth','eleventh','twelfth'];
  lv_gifts LYRIC[] := ARRAY[
    ROW('','partridge in a pear tree')::lyric,
    ROW('Two','turtle doves')::lyric,
    ROW('Three','French hens')::lyric,
    ROW('Four','calling birds')::lyric,
    ROW('Five','golden rings')::lyric,
    ROW('Six','geese a laying')::lyric,
    ROW('Seven','swans a swimming')::lyric,
    ROW('Eight','maids a milking')::lyric,
    ROW('Nine','ladies dancing')::lyric,
    ROW('Ten','lords a leaping')::lyric,
    ROW('Eleven','pipers piping')::lyric,
    ROW('Twelve','drummers drumming')::lyric
  ];
  lv_song  TEXT[];
  verse_line TEXT;
BEGIN
  lv_song := twelve_days(lv_days, lv_gifts);

  FOREACH verse_line IN ARRAY lv_song LOOP
    IF verse_line IS NOT NULL AND LENGTH(TRIM(verse_line)) > 0 THEN
      RAISE NOTICE '%', verse_line;
    ELSE
      RAISE NOTICE '';
    END IF;
  END LOOP;
END;
$$;