$sth->execute();
my @fields = @{$sth->{NAME}};
[download]

sub Fetch_w_callback{ # Main DBI retrieval mechanism----
   my ($sql, $callback) = @_;
   my $state = $_[2] ||= {}; # ** This THIRD param is CALL-by-ref **
   $debug{SQL} = $sql;
   (my $sth= $state->{STH} = $dbh->prepare($sql))->execute();
   $state->{ROW} = $state->{QUIT} = 0;
   while (my $row = $sth->fetchrow_hashref()){
      $debug{ROW} = $state->{ROW}++;
      #last if ($callback->($row,$state) || 0) < 0; # Return negative 
+to quit
      $callback->($row,$state);
      last if $state->{QUIT}; # Call-ee asked us to stop.
   }
   $sth->finish;
 }
[download]

   my $sql= "SELECT blah blah....";
   Fetch_w_callback( "COPY ($sql) TO STDOUT CSV HEADER", # Let pg do c
+sv work 
      sub {
           my ($row,$state) = @_;
           print $row,"\n";
           ##$fieldnames ||= $state->{STH}->{NAME} || [keys %$row]; 
           ##if ($state->{ROW} ==1 ){
           ##      print $state->{HEADER} = 
           ##            join(",",@$fieldnames, "SOURCE=\@$ENV{HOSTNAM
+E}"
           ##                      . " on " . scalar(localtime(time)))
+  ."\n"; # Header row  
           ##}                                          
           ##
           ##print join(",", map{defined $_?$_:""}@$row{@$fieldnames})
+ ."\n";
           return 0;
        },
        $state
      );
[download]

If you use a hashref as the slice arg to fetchall_arrayref you will receive an AoH rather than an AoA. Then it becomes trivial:

my $query = $connect->prepare ("SELECT * FROM TableName");
$query->execute ();
my $data = $query->fetchall_arrayref ({});
for my $row (@$data) {
    for my $key (keys %$row) {
        print "Column name is $key, column value is $row->{$key}\n";
    }
}
[download]

Note that fetching hashrefs is (much) slower than fetching arrayrefs. Most often this is marginal compared to the time spent in getting the data from the database, but in many cases, you can bring the speed back by using bind_columns:

my $sth = $dbh->prepare ("select * from foo");
$sth->execute;
my (@fields, %r) = @{$sth->{NAME_lc}};
$sth->bind_columns (\@r{@fields});
while ($sth->fetch) {
    # Do something with the record. Fields are stored in %r
    printf "%-16s: %s\n", $_, $r{$_} // "--undef--" for @fields;
    }
[download]


Enjoy, Have FUN! H.Merijn

Dear all, Thank you very much for all your suggestions. Went with fetchall_arrayref for now and does exactly what I need. Will try out the other solutions later as well. Again, thank you!

From another perspective, if you need to look up what columns are in a table, you might find the DBI $dbh->table_info method helpful. After gathering a list of available columns, you can build a query with known columns in a known order, or use bind_columns to bind hash values with the column names as keys; the DBI documentation has an example of this in the description for bind_columns.

Maybe I'm misunderstanding the question, but this looks like a typical application for fetchrow_hashref or fetchall_hashref .

 $hash_ref = $sth->fetchrow_hashref;

An alternative to fetchrow_arrayref . Fetches the next row of data and returns it as a reference to a hash containing field name and field value pairs. Null fields are returned as undef values in the hash.

$hash_ref = $sth->fetchall_hashref($key_field);

The fetchall_hashref method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to a hash containing a key for each distinct value of the $key_field column that was fetched. For each key the corresponding value is a reference to a hash containing all the selected columns and their values, as returned by fetchrow_hashref().