column_info

Warning: This method is experimental and may change. 
  $sth = $dbh->column_info( $catalog, $schema, $table, $column );


Returns an active statement handle that can be used to fetch informati
+on about columns in specified tables
[download]

column_info is the generic answer.

Unfortunately I've found that only a few DBD drivers actually implement it (for example DBD::Sybase will only implement this in the next release...)

Michael

The DBI defines methods for getting metadata. If you want to get info on a given table, though, you have to go through a statement handle. It's a little bit involved after that. You execute a "select * from table_name" (or, if you know the field names you're interested in, just get those) and then the driver makes other info available to you via the {NAME} and {TYPE} attributes of the statement handle.

# you've already got your DB handle:

my @tables = $db->tables();
foreach my $table (@tables) {
    my $sql = "select * from $table";
    my $sth = $db->prepare( $sql );
    $sth->execute(); # check for errors, either set RaiseError or have
+ a "die" clause here
    print "Structure of $table \n\n";
    my $num_fields = $sth->{NUM_OF_FIELDS};
    for ( my $i=0; $i< $num_fields; $i++ ) {
        my $field = $sth->{NAME}->[$i];
        my $type = $sth->{TYPE}->[$i];
        my $precision = $sth->{PRECISION}->[$i]; # e.g. VARCHAR(50) ha
+s a precision of 50
        print "Field $field is of type $type, with precision $ precisi
+on\n";
    }
    $sth->finish();
}
[download]

You can turn those numeric types into more descriptive names, I don't know anything specifically about DBI::Pg, and my knowledge is derived from the very useful Programming the Perl DBI, which has been surpassed on the metadata issue via newer versions of the DBI, but I see that those newer versions of the DBI define type_info methods that can help you figure out which types you're dealing with.

I mistrust all systematizers and avoid them. The will to a system shows a lack of integrity -- F. Nietzsche

Returns a reference to an array of integer values for each column. The integer shows the storage (not display) size of the column in bytes. Variable length columns are indicated by -1.

- Programming the Perl DBI