I have spent most of the day scratching my head over this one. I have a PostgreSQL database which has an “events” table, within which there is a “logdate” column (which is a simple date field). I need to be able to run an SQL query which gives me all events older than a certain length of time (e.g. an interval of so many days, weeks or months). The standard way to do this is to prepare a query with a “placeholder” into which a string supplied by the user can be safely inserted.
I am using Perl for this project and initially I was trying this with the excellent DBIx::Class
module (which uses the SQL::Abstract
module internally). The weirdness of the problem had me convinced there was a bug somewhere so I spent a while upgrading everything to the latest versions in the hope that the issue would disappear. When this did not help I reduced the problem to the simplest version possible using the standard DBI
interface, like below:
use DBI my $dbh = DBI->connect("dbi:Pg:dbname=buzzsaw;"); $sth = $dbh->prepare( "select count(*) from event where ( logdate < current_date - interval ?)"); $sth->execute("26 weeks")'
but this still produces an error message like this:
DBD::Pg::st execute failed: ERROR: syntax error at or near "$1" LINE 1: ...(*) from event where ( logdate < current_date - interval $1) ^ at -e line 1.
At this point it became clear that this is a “feature” or, at the very least, the lack of a feature rather than a bug. A bit of hunting around revealed this ancient Debian bug report from September 2006. Thankfully the bug report does contain a work-around which is to use the placeholder as a string and then cast it to an interval like this:
use DBI my $dbh = DBI->connect("dbi:Pg:dbname=buzzsaw;"); $sth = $dbh->prepare( "select count(*) from event where ( logdate < current_date - ?::interval)"); $sth->execute("26 weeks")'
Given the grief this caused me I thought it worth committing to my blog in the hope that the next person to hit this issue will find the solution quicker than I did. This is definitely a lesson in why the best approach is to reduce a problem to its simplest form rather than just assuming something has a bug.