Please tell us more about your requirement.   As you know, DBI already handles the “mechanics” of interfacing with Postgres (or any other ...) database, so the question before the house appears to be, “what’s a good way to let the user specify which query he wants to run?”   A command-line parameter value is a fine way to do it.   I would suggest that the main-program chooses among 12 different subroutines to call (thus keeping the main small and simple), and that each of these 12, insofar as possible, calls one or more common subroutines to do whatever turns out to be common among all of them.

This is also a fine time to learn about packages and classes, because these are an excellent way to provide for “common functionality.”   (Maybe, instead of calling a subroutine, the main routine instantiates an instance of one of 12 classes, each of which inherits from a common ancestor, and tells that instance to, say, run().   The specific subclass encompasses what is unique about a particular request, while the parent class from which they all derive encompasses what is common between them all.

Both replies show the sort of different approach I'm looking for.

The application will run remote scans on a group of a few thousand servers. It checks for software version and confirms function. The current monstrosity is written in /bin/sh and lacks a lot of finesse, but did provide the basic remote command set, which I am going to leverage through a new Perl-based foundation code.

Anyway, the way it will work is this: Command invoked with no flag will initiate a scan of the entire environment of anything not scanned in the last 24 hours. There will need to be command-line switches and/or arguments to specify groups of servers based on admin owner or environment, or date since last scan, or specific result codes kept in the datatabase with the scan record. In any of these cases, it will be necessary to make a call to the database to assemble a list of hosts for scanning.

So in the case of the user wanting to scan all dev hosts belonging to the Java team that scored less than 400 on the scan and haven't been scanned in one month, the command line might look like:

Scanner.pl -O java -E dev -R 400 -D 1M <\p>

Thanks for the suggestions so far. I'm getting inspired.

G'day justsomeguy,

Here's some skeleton code that offers a general approach you could take. Also, given your request is a little on the vague side, I've provided a few alternative paths you may like to investigate.

Put your utilities in a module. I've used an OO module; a functional module may better suit your needs. I've shown hand-crafted constructor and accessor methods for demonstration purposes; you may prefer Moose (or similar). Also, I've used DBI (as indicated in your OP); other modules (e.g. DBIx::Class) may be a better choice for you.

package Your::SQL::Utils;

use DBI;
use DBD::Pg;

sub new {
    my ($class, ...) = @_;
    bless { dbh =>  _connect() } => $class;
}

sub _connect {
    ...
    return $dbh;
}

sub dbh { 
    my ($self) = @_;
    $self->{dbh};
}

sub some_query {
    my ($self, ...) = @_;

    ...
    my $sth = $self->dbh->prepare(...);
    ...
    $sth->execute(...);
    ...
}

sub other_query { ... }
[download]

Your script might look something like this. (Here's the Getopt::Long documentation in case you're unfamiliar with this core module.)

# your_prog.pl

use Your::SQL::Utils;
use Getopt::Long;

my $query;
GetOptions('query=s' => \$query) or die "...";

my $util = Your::SQL::Utils::->new();
$util->$query();
[download]

Serving suggestion:

$ your_prog.pl -q some_query
[download]