Sometimes you have an SQL-Statement that you want to run on all the databases on your server. Here is the run_sql_on_all_databases.pl script to do so:

  #!/usr/bin/perl
  
  use strict;
  use warnings;
  use DBI;
  
  my $driver   = "Pg";
  my $database = "postgres";
  my $dsn      = "DBI:$driver:dbname=$database";
  my $userid   = "sql-ledger";
  my $password = "";
  
  my $dbh = DBI->connect( $dsn, $userid, $password, { RaiseError => 1 } )
    or die $DBI::errstr;
  
  sub run_queries_on_all_dbs {
      my @queries = @_;
  
      my $max_query_length = 0;
      for my $query (@queries) {
          $max_query_length = length($query) if length($query) > $max_query_length;
      }
  
      printf "+-%-30s-+-%-10s-+-%-${max_query_length}s-+\n", "-" x 30,        "-" x 10, "-" x 
  $max_query_length;
      printf "| %-30s | %-10s | %-${max_query_length}s |\n", "Database Name", "Rows",   "Query";
      printf "+-%-30s-+-%-10s-+-%-${max_query_length}s-+\n", "-" x 30,        "-" x 10, "-" x 
  $max_query_length;
  
      my $dbs = $dbh->selectcol_arrayref("SELECT datname FROM pg_database WHERE datistemplate = false;");
  
      for my $db (@$dbs) {
          my $database_dsn = "DBI:$driver:dbname=$db";
          my $dbh          = DBI->connect( $database_dsn, $userid, $password, { RaiseError => 0, PrintError 
  => 0 } );
  
          if ( !$dbh ) {
              warn "Failed to connect to database '$db'. Error: $DBI::errstr\n";
              next;
          }
  
          for my $query (@queries) {
              my $rv = $dbh->do($query);
              if ( !defined $rv ) {
                  warn "Failed to run query on database '$db'. Error: $DBI::errstr\n";
              } else {
                  printf "| %-30s | %-10d | %-${max_query_length}s |\n", substr( $db, 0, 30 ), $rv, $query;
              }
          }
  
          $dbh->disconnect();
      }
  
      # Print table footer
      printf "+-%-30s-+-%-10s-+-%-${max_query_length}s-+\n", "-" x 30, "-" x 10, "-" x $max_query_length;
  }
  
  run_queries_on_all_dbs( "DELETE FROM gl WHERE id NOT IN (SELECT DISTINCT trans_id FROM acc_trans)", );
  
  $dbh->disconnect();