my $dbh   = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }
+) or die $DBI::errstr;
my $query = "SELECT * FROM logs_column WHERE logged_in > NOW()::timest
+amp - interval '24hour'";
my $sth   = $dbh->prepare($query);

$sth->execute();

while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}
[download]

Hope this helps!

Looking at the DBI documentation, I don't think your usage of $dbh->($query) is supported. What error message do you get?

If you are mainly looking at producing a report from an SQL query, Querylet has good and simplistic support for creating CSV files.

If this is only a stepping stone to do more with the data than just print it, but you want to inspect the data visually, there is DBIx::RunSQL->format_results , which returns a query result formatted as an ASCII table.

The example usage shows the following code, which could also be a suitable guideline for you for how to run a query using DBI:

my $sth= $dbh->prepare( 'select * from foo' );
$sth->execute();
print DBIx::RunSQL->format_results( sth => $sth );
[download]

#!/usr/bin/env perl

use strict;
use warnings;
use Data::Dumper;

my $driver  = "Pg"; 
my $database = "dbase";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432
+";
my $userid = "postgres";
my $password = "postgres";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1, Au
+toCommit => 0 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

my $selsth = $dbh->prepare("SELECT * FROM logs_column WHERE logged_in 
+> NOW()::timestamp - interval '24 hours'")
    or die($dbh->errstr);

if(!$selsth->execute) {
    die($dbh->errstr);
}

my @lines;
while((my $line = $selsth->fetchrow_hashref)) {
    # push hashref of each row into @lines
    push @lines, $line;
}
$dbh->commit;

# To check if this is the structure you want:
print Dumper(\@lines);

$dbh->disconnect;

...

open(my $ofh, '>', 'bla.log') or die($!);
foreach my $line (@lines) {
    print $ofh join(';', $line->{timestamp}, $line->{logtext}), "\n";
}
close $ofh;
[download]

Something like that?

If this is all you want to do, you don't need Perl, you can just use psql -c.

You are correct. And I can run that with the -o /home/user/date.txt Works well if I run it manually. But, I need it to run as a cron job every night at 23:59, and add the date as the filename. ie...09012024.txt 09022024.txt 09032024.txt

I need it to run as a cron job every night at 23:59, and add the date as the filename.

That's easy with a shell script. Replace -o /home/user/date.txt with -o "/home/user/$(date '+%m%d%Y').txt". Replace the format string passed to the date command with '+%Y%m%d' to get easily sortable file names instead of that M/D/Y mess.

(Yes, I prefer using perl over shell, but sometimes, the shell is just more efficient.)

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)