Miguel has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed monks,

I'm trying to get complete information about some tables with DBI and DBD::Pg.
I'm doing it this way: 

my @tables = qw/table1 table2 table3/;

foreach my $tab ( @tables ) {

   my $attrs = DBH->func($tab, 'table_attributes');
    print $q->p,
    $q->strong($_),
    $q->start_table({BORDER=>1}),
    $q->TR(
        $q->th([qw/NAME TYPE SIZE NULLABLE DEFAULT CONSTRAINT PRIMARY_
+KEY/])
       );

     foreach my $item ( @{$attrs} ) {
      print $q->TR(
       $q->td(
        [
         %{$item}->{NAME},
         %{$item}->{TYPE},
         %{$item}->{SIZE},
         %{$item}->{NULLABLE},
         %{$item}->{DEFAULT},
         %{$item}->{CONSTRAINT},
         %{$item}->{PRIMARY_KEY},
        ]
       )
      );
 }
 print $q->end_table;
}

[download]
And I get something similar to this: 
NAME   TYPE   SIZE   NULLABLE   DEFAULT
idfield  4      4        0        nextval('public.table1_idfield_seq':
+:text)
(some more rows)

[download]

My problem is: How do I get TYPE values translated into TYPE_NAMES, instead their numbers?

I've been playing with type_info() but it is too complicated for my little experience dealing with data structures :-(

Any kind monk would give me a hand on this?

Or, perhaps, there's some better way to get this done and I don't know about it.

Thanks in advance,
Miguel

UPDATE
Thank you very very much!

Replies are listed 'Best First'.
Re: how to get complete information about tables
by insaniac (Friar) on Dec 07, 2004 at 22:05 UTC
    like this for instance: 
    use DBI;
use CGI;
my $dbname         = "dbname";
my $user           = "user";
my $password       = "password";


my @tables = qw/table1 table2/;
my $q = new CGI;
my $dbh = DBI->connect("DBI:Pg:dbname=$dbname","$user","$password", {
PrintError => 0, RaiseError => 0, AutoCommit => 1 }) || die "Could not
connect to database:\n";

foreach my $tab ( @tables ) {

   my $attrs = $dbh->func($tab, 'table_attributes');
    print $q->p,
    $q->strong($_),
    $q->start_table({BORDER=>1}),
    $q->TR(
        $q->th([qw/NAME TYPE SIZE NULLABLE DEFAULT CONSTRAINT PRIMARY_
+KEY/])
       );

     foreach my $item ( @{$attrs} ) {
        my $type_name = %{$dbh->type_info(%{$item}->{TYPE})}->{TYPE_NA
+ME}  ;
      print  "\n",$q->TR(
       $q->td(
        [
         %{$item}->{NAME},
         #%{$item}->{TYPE},
         $type_name,
         %{$item}->{SIZE},
         %{$item}->{NULLABLE},
         %{$item}->{DEFAULT},
         %{$item}->{CONSTRAINT},
         %{$item}->{PRIMARY_KEY},
        ]
       )
      );
 }
 print $q->end_table;
 }

    [download]
    works for me ;-) 
     NAME            TYPE            SIZE            NULLABLE        DEFAU
+LT         CONSTRAINT      PRIMARY_KEY
        bonsai_id       int4            4               0             
+  nextval('public.bonsais_bonsai_id_seq'::text)                   1
        name            text            40              1             
+                                  0
        type_id         int4            4               1             
+                                  0
        purchase_date   bytea           4               1             
+                                  0
        birthdate       bytea           4               1             
+                                  0
        shop            text            80              1             
+                                  0
        comment         text                            1             
+                                  0

    [download]
    --
    to ask a question is a moment of shame
    to remain ignorant is a lifelong shame
[reply]
[d/l]
[select]
      Your line 
      $type_name = %{$dbh->type_info(%{$item}->{TYPE})}->{TYPE_NAME}

      [download]
      almost worked fine...

      I get 

      Can't use an undefined value as a HASH reference at that line

      [download]
      when gets DATE or TIME data types.

      On the other hand, I get type=text when expecting type=varchar

      What am I missing here?

      --------UPDATE----------------

      Actually, it was with NUMERIC data types, not DATE nor TIME.
      For these, I get TYPE=bytea, instead DATE or TIME.

      But, if I change the line to 

      $type_name = %{$dbh->type_info(%{$item}->{TYPE})}->{SQL_DATETIME_SUB};

      [download]
      or 
      $type_name = %{$dbh->type_info(%{$item}->{TYPE})}->{SQL_DATA_TYPE};

      [download]
      I get NULL values.

      From the DBD::Pg documentation, I read "Information is only provided for SQL datatypes and for frequently used datatypes."
      Question: are DATE and TIME so infrequently used datatypes like that?

      Isn't there any better way to get all information about DB tables?

[reply]
[d/l]
[select]
        i'm sorry.. i don't know, i just used the example of type_info doc in order to retrieve the typenames. But if they are correct or not, I don't know. This is just what type_info outputs...
        and i won't be able to search for an answer today.. since i'm teaching all day long... tonight I can search for an answer, but maybe another monk (i'm just a mere scribe) knows the answer..
        --
        to ask a question is a moment of shame
        to remain ignorant is a lifelong shame
[reply]

Back to Seekers of Perl Wisdom