There isn't a general purpose DESCRIBE or SHOW TABLES in Pg's sql syntax if you aren't using the command line psql tool. ( try \dt and \d tablename if you are).

You can peruse the source for psql (look at describe.c), or use the -E option to psql to dump it's internal SQL commands it uses for \dt and \d.

For the lazier folks, here's postgres running \dt and \d on a table called fees, along with the SQL commands that \d and \dt use behind the scenes

$dbh->tables;
[download]

$sql = "select * from $table"
[download]

$sth->{NAME}->[0];
[download]

rdfield

DBI does allows the user to get a list of tables through the $dbh->tables command.

The way I get the column names is just run a bogus search: "select * from $table where 1 = 0". This should run very quickly and get me the information I want.

Here's a sample program:

#!/usr/bin/perl -w

use strict;
use DBI;

my ($driver, $dbh, @tables);

$driver="DBI:CSV:f_dir=Tables";

$dbh=DBI->connect($driver) or die "Can't connect:  $DBI::errstr";

eval {
   @tables = $dbh->tables;
   @tables = grep { s/.*\.//o } @tables;

} or die "Couldn't retrieve list of tables: $DBI::errstr\n";

for my $thisTable (@tables) {
   eval {
      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;
   } or die "Couldn't read table $_: $DBI::errstr\n";
}

$dbh->disconnect();
[download]

• My tables came out with names like "me".tablename, so I had to prune off the username part (I don't know how other DBDs will write this).
• The $dbh->tables function may not be supported. The DBI documentation says "This method is experimental and may change or disappear".

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

Postgres stores all its table names in a table, pg_tables.

To get a list of all non system tables using SQL try:

select * from pg_tables where tablename not like 'pg_%' 

Ta ta

> To get a list of all non system tables using SQL try:

> select * from pg_tables where tablename not like 'pg_%' <CODE>

Which is fine until you switch to another database in the future, at which time you have to modify the script again.

In general, I choose to try to always use non-vendor specific solutions. Come to think of it, that's why DBI exists in the first place!

rick