I'm about to release a new version of SQL::Statement with many new features including vastly improved parens parsing (thanks Dean Arnold), column aliases (thanks Robert Rothenberg), user-defined functions, and heterogenous joins across any DBI accessible RDBMSs. I'd much appreciate if people could test it. Please grab the distribution and try out the user-defined functions. Also, please test as many hairy SQL statements as you can to test the new parentheses parsing (i.e. lots of nested WHERE clauses). Testing can be done either with SQL::Statement by itself, or with any DBD that subclasses it (CSV,DBM,File,Excel,AnyData,etc.)

In addition to the testing, I am calling for submissions of new built-in functions. I've built a couple of them, but why hog all the fun for myself? Here's POSITION() as an example, but I don't have SQRT(), IFNULL() and many others that would be easy to create.

    sub SQL_FUNCTION_POSITION {
        my($sth,$rowhash,@params) = @_;
        return index($params[1],$params[0]) +1;
    }
    # e.g. SELECT col1 FROM tbl WHERE POSITION('foo',col2) < 3;
[download]

Or, here's SOUNDEX() which I implemented on theorbtwo's suggestion:

sub SOUNDEX {
  my($self,$sth,$rowhash,@params)=@_;
  use Text::Soundex 'soundex';
  my $s1 = soundex($params[0]) or return 0;
  my $s2 = soundex($params[1]) or return 0;
  return $s1 eq $s2;
}
[download]

Submit a built-in function, get your name in the Functions module :-).

And if you aren't having fun yet, try playing with user-defined Table-generating functions that allow you to dynamically create tables from anything and to perform heterogeneous joins across RDBMs (e.g. join a PostgreSQL table to a MySQL table or join two XBase tables even though XBase itself doesn't support table joins).

Here's a complete script that creates and queries two in-memory tables.

  use DBI;
  my $dbh = DBI->connect('dbi:File(RaiseError=1):');
  $dbh->do("CREATE FUNCTION $_ EXTERNAL") for qw(Prof Class);
  sub Prof  {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]
+}
  sub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]
+}
  $sth = $dbh->prepare("SELECT * FROM Prof NATURAL JOIN Class");
  $sth->execute;
  $sth->dump_results;
[download]

Table-generating functions only need to return an AoA, so build a function to scrape an RSS feed or a file system directory listing, or whatever into an AoA and then query the data with SQL. More examples in SQL::Statement::Functions.pm.

There are special built-in functions _0 through _9 that support heterogeneous queries and per-table RDBMS connections. Assuming that $pg_sth is any prepared and executed PostgreSQL data-returning statement handle and $mysql_sth is the same for MySQL, here's how an example of a heterogeneous join:

$sth = $dbh->prepare("SELECT * FROM _1(?) AS x NATURAL JOIN _2(?) AS y
+");
$sth->execute( $pg_sth, $mysql_sth);
[download]

Have the appropriate amount of fun!