I am forced to port some code over to a PostgreSQL database that was once using a MySQL database. At first I thought that it would be a simple task of changing the db_driver for the DBD module. However, apparently PgSQL does not share some of the commands I was using on the MySQL database.
Specifically, major portions of the code pivot on a few lines that use something like: 
…
my $sth = $dbh->prepare("show tables") or &bail($DBI::errstr);
my $rv = $sth->execute or &bail($DBI::errstr);
while ( ( my $table_name ) = $sth->fetchrow_array ) {
  $sth2 = $dbh->prepare("describe $table_name") or die print "Can't do
+ select: $DBI::errstr";
  $rv2 = $sth2->execute or die print "Can't execute the query: $DBI::e
+rrstr";
  while (($field_, $type_, $null_,$key_, $default_, $extra_) = $sth2->
+fetchrow_array) {
    #stuff with cols and then rows.
  }
}
…

[download]
Or… (credit to athomason's dump code)… 
…
# Get a list of the tables
my @table_names;
eval {
    @table_names = @{$dbh->selectcol_arrayref("SHOW TABLES")};
    1;
} or die "Couldn't retrieve list of MySQL tables: $DBI::errstr";

# Get data from each table
my (@tables, $table);
eval {
    foreach $table (@table_names) {
        # Record how the table is layed out
        my $layout = $dbh->selectall_arrayref("DESCRIBE $table");
        # Store the data in a 2D array reference
        my @fields = map ${$_}[0], @{$layout};
        my $data = $dbh->selectall_arrayref("SELECT " .
                                            (join ',', @fields) .
                                            " FROM $table");
        push @tables, [$layout, $data];
    }
    1;
} or die "Failed while obtaining data from $table: $DBI::errstr";
…

[download]
DBD::Pg::db selectcol_arrayref failed: ERROR: Option 'tables' is not recognized.
DBD::Pg::st execute failed: ERROR: parser: parse error at or near "describe".

I guess that SHOW does something different in PgSQL than in MySQL and that DESCRIBE is non existent in PgSQL.
Any ideas on how I can get the same results from DBI or simple SQL commands so that I can get the same output from a Postgres database that I was getting from a MySQL database? So that I can just modify those lines which use SHOW and DESCRIBE and not have to touch the rest of the applications.

Thanks, -Jeff

In reply to DBI - MySQL vs. PgSQL by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.