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