Actually, you should be able to just use:

#!/usr/local/bin/perl -w

use strict;
use DBI;
use DBD::Pg;

my ($dbh, @tables);
my $user = "username";
my $password = "";
my $db_name="database";
my $driver="Pg";
my $db_host="localhost";
my $dsn="DBI:$driver:dbname=$db_name;host=$db_host";

$dbh=DBI->connect($dsn,$user,$password) or die "Can't connect:  $DBI::
+errstr";

@tables = $dbh->tables;

for my $thisTable (@tables) {
      my $sth=$dbh->prepare("select * from $thisTable where 1 = 0");
      $sth->execute();
      my $columnRef=$sth->{NAME_lc};
      for my $thisColumn (@$columnRef) {
            print "Table: $thisTable, column is: $thisColumn\n";
            # Do whatever else you want with the columns.
      }
      1;
}

$dbh->disconnect();
[download]

-

@tables = grep { s/.*\.//o } @tables;
[download]

Would probably complain with use strict about cat’ing to an undef…. Right? I guess that is why you used evals though.
And $dbh->tables with DBD:Pg will just return an array of table names anyway. Of course, as the DBI perldoc states: “Warning: This method is experimental and may change.”
So, although I agree with quikwit that you should try to write things as portable as possibly… to some extent you will not be able to (tables() returning different things with CSV vs. Pg.) unless you have all possibly platforms to test/write on.
I did not know that SHOW was not in the SQL92 specs. either. ++quikwit for pointing that out. I have been using SHOW TABLES myself.

If you want to access fields in the columns as DESCRIBE may have done, you should check out my question on feeding fields into an array: Just another DBI question.
Ciao, -xtype