-- Create the extension
CREATE EXTENSION plperlu;

-- define how the table row returned by the perl function looks like
CREATE TYPE poemrow AS (
    poemrowid bigint,
    verserowid bigint,
    verseline text
);

-- Automatic poem generator
CREATE OR REPLACE FUNCTION bottlesofbeer(bigint) RETURNS SETOF poemrow AS $$
    my ($bottlecount) = @_;

    if($bottlecount < 0) {
        $bottlecount = 0;
    };

    my $rowid = 0;

    my @verselines = (
        'COUNT bottles of beer on the wall, COUNT bottles of beer.',
        'Take one down, pass it around!',
        'REDUCED bottles of beer on the wall',
        ''
    );

    my $vcount;
    while($bottlecount) {
        my $nextcount = $bottlecount - 1;

        $vcount = 1;
        foreach my $rawline (@verselines) {
            my $line = '' . $rawline; # COPY the line
            $line =~ s/COUNT/$bottlecount/g;
            $line =~ s/REDUCED/$nextcount/g;

            return_next({poemrowid => $rowid, verserowid => $vcount, verseline => $line });

            $vcount++;
            $rowid++;
        }

        $bottlecount--;
    }

    return_next({poemrowid => $rowid, verserowid => $vcount, verseline => 'No more beer, buy some more!' });
            
    return undef; # We are done here
$$ LANGUAGE plperlu;