-- 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;

##</code><code>##

ER_DEV_DB=# SELECT * FROM bottlesofbeer(3);
 poemrowid | verserowid |                     verseline                     
-----------+------------+---------------------------------------------------
         0 |          1 | 3 bottles of beer on the wall, 3 bottles of beer.
         1 |          2 | Take one down, pass it around!
         2 |          3 | 2 bottles of beer on the wall
         3 |          4 | 
         4 |          1 | 2 bottles of beer on the wall, 2 bottles of beer.
         5 |          2 | Take one down, pass it around!
         6 |          3 | 1 bottles of beer on the wall
         7 |          4 | 
         8 |          1 | 1 bottles of beer on the wall, 1 bottles of beer.
         9 |          2 | Take one down, pass it around!
        10 |          3 | 0 bottles of beer on the wall
        11 |          4 | 
        12 |          5 | No more beer, buy some more!
(13 rows)

ER_DEV_DB=# SELECT verseline FROM bottlesofbeer(8) WHERE verserowid = 3 ORDER BY poemrowid DESC;
           verseline           
-------------------------------
 0 bottles of beer on the wall
 1 bottles of beer on the wall
 2 bottles of beer on the wall
 3 bottles of beer on the wall
 4 bottles of beer on the wall
 5 bottles of beer on the wall
 6 bottles of beer on the wall
 7 bottles of beer on the wall
(8 rows)


##</code><code>##

-- Create a perl trigger function in database schema "pos"
CREATE OR REPLACE FUNCTION pos.customersinserttrigger() RETURNS trigger AS $$
    use strict;

    if(defined($_TD->{new}->{'customer_number'}) && $_TD->{new}->{'customer_number'} ne '') {
        return;
    }

    my $selsth = spi_prepare("SELECT max(customer_number) AS max_customer_number FROM pos.customers WHERE customer_number IS NOT NULL");
    my $nextnum = 1_000_000;

    my @lines;
    {
        my $retval = spi_exec_prepared($selsth);
        if($retval->{status} ne 'SPI_OK_SELECT') {
            elog(ERROR, "Failed to select all rows in pos.customers");
        }

        if($retval->{processed}) {
            @lines = @{$retval->{rows}};
        }
        spi_freeplan $selsth;
    }

    foreach my $line (@lines) {
        my $tmp = $line->{max_customer_number};
        $tmp = 0 + $tmp;
        if($tmp > $nextnum) {
            $nextnum = $tmp;
        }
    }
    $nextnum++;

    $_TD->{new}->{'customer_number'} = $nextnum;

    return "MODIFY"; # EXECUTE, all updates sucessfull

$$ LANGUAGE plperlu;

-- Add a trigger to table "customers" in schema "pos" to call our trigger function on every inserted or updated row
CREATE TRIGGER insertupdate_customers_trigger
    BEFORE INSERT OR UPDATE ON pos.customers
    FOR EACH ROW EXECUTE FUNCTION pos.customersinserttrigger();