I was annoyed by the verbosity (logorrhoea) of my above "solution" and I wanted to figure out a better way to trace only one or a few statement. (i.e. switch tracing on and off)

I was going to try for other DB's than postgres as well but as I don't find the time to dig those details up (surely it must be possible) here is at least a better way for DBD::Pg (postgresql).

For postgres:

$dbh->{trace} = 'SQL'; turns SQL tracing on

$dbh->{trace} = 0; turns tracing off.

I'd like to see how such switching is done in other DBD's.

#!/opt/perl-5.20/bin/perl
use strict;
use warnings;
use DBI;
main();
exit;
sub main {
  my $dbh = DBI->connect or die "oops - $!\n";
     $dbh->{RaiseError} = 1;

  # if table public.t does not exist, create it:
  if ($dbh->selectrow_arrayref( "
        select count(*)
        from information_schema.tables
        where table_schema = 'public'
          and table_name = 't' " )->[0] == 0)
  {
    $dbh->do("create table public.t as select id from generate_series(
+1,20) as f(id)");
  }
  my $sql = "select id from t where id = ?";
  my $sth = $dbh->prepare($sql)  or die "oops - $!\n";
  trace_test($sth, 'SQL'   , 10);
  trace_test($sth,  undef  , 11);
  trace_test($sth,   0     , 12);
  trace_test($sth, 'SQL'   , 1000);
}
sub trace_test {

  my ($sth, $trace, $id) = @_;

  if (defined $trace) { 
        $sth->trace($trace);
  }
 
  print "--> trace_test() start.  trace = ";
  print $sth->trace;
  print ", passed '", (defined $trace ? $trace : 'undef'), "'\n";

  my $rc = $sth->execute($id);
  while (my $rrow = $sth->fetch) { print "", $rrow->[0], "\n"; }
  print "\n";
  $sth->trace(0); # always switch off
}
[download]

Now we only get ouptut where we asked for it:

--> trace_test() start.  trace = 256, passed 'SQL'
EXECUTE select id from t where id = $1 (
$1: 10
);

10

--> trace_test() start.  trace = 0, passed 'undef'
11

--> trace_test() start.  trace = 0, passed '0'
12

--> trace_test() start.  trace = 256, passed 'SQL'
EXECUTE dbdpg_p15160_1 (
$1: 1000
);
[download]