Regarding what you're doing with set_sql here, this is also pretty unusual. The set_sql stuff is supposed to be part of the class definition and it adds permanent class methods to your CDBI module. Changing these on the fly means you probably should be doing this part in some other way, like using DBI directly.

I must say that, albeit in my ignorance, I had a perception that this was the case - i.e.: I didn't have the slightest idea of what the solution may have been, but I understand what is the problem.

I don't quite understand why you're overwriting this query. Are you trying to have subclasses that all define this query differently?

Indeed I'm not overwriting it. I do not want to inadvertently overwrite it. That is the problem I refer to above.

Now, your suggestion which I'm understanding more and more is to skip set_sql() altogether, since I have other ways to get the only thing that it really buys me, that avoid its side effects (and I take this to be the heart of your comment to my code). For the moment I have devised a patch. As time permits and I become more confident with this stuff, I'll modify all existing modules written in this style at once.

One thing that I do is put the reporting queries for my tables into their respective CDBI classes for organizational purposes. It helps keep all the related queries together. Then I make a method in the class that runs the query and returns the result. Here's a small example:

# in package DB::Contribition

__PACKAGE__->set_sql(average_dollars => <<'END_SQL' );
SELECT AVG(amount)
FROM   contribution
END_SQL

sub average_dollars {
    my $class = shift;
    my $sth  = $class->sql_average_dollars();
    $sth->execute();
    return $sth->select_val() || 0;
}
[download]