Disclaimer: very first time I have used DBI.

I've got a MySQL table with many different indexed fields (f1, f2, f3, etc) that are utilized to generate WHERE clauses by lengthy-running processes that iterate over portions from the database carrying out various cleaning and testing procedures.

The present version of the code works something similar to this:

sub get_list_of_ids() {
    my ($value1, $value2, $value3...) = @_;

    my $stmt = 'SELECT * FROM files WHERE 1';
    my @args;

    if (defined($value1)) {
        $stmt .= ' AND f1 = ?';
        push(@args, $value1);
    }
    # Repeat for all the different fields and values

    my $select_sth = $dbh->prepare($stmt) or die $dbh->errstr;
    $select_sth->execute(@args) or die $select_sth->errstr;

    my @result;
    while (my $array = $select_sth->fetch) {
        push(@result, $$array[0]);
    }
    return \@result;
}

sub function_A() {
    my ($value1, $value2, $value3...) = @_;

    my $id_aref = get_list_of_ids($value1, $value2, $value3...);
    foreach my $id (@$id_aref) {
        # Do something with $id
        # And something else with $id
    }
}

sub function_B() {
    my ($value1, $value2, $value3...) = @_;

    my $id_aref = get_list_of_ids($value1, $value2, $value3...);
    foreach my $id (@$id_aref) {
        # Do something different with $id
        # Maybe even delete the row
    }
}

Anyway, I am going to dump a great deal more rows within the database, and am knowledgeable the code above wont scale up. I'm able to think about a number of ways to repair it according to other languages. What's the easiest method to handle it in Perl?

Tips to notice are the logic in get_list_of_ids() is simply too lengthy to duplicate in every function which the procedures around the selected rows are extremely varied.

Thanks ahead of time.