How can I use a query with a placeholder inside quotes? (perl / postgresql)

Question

I am trying to execute the following script:

#!/usr/bin/perl -w use strict; use DBI; my $db = "Pg"; my $db_database = "whatever"; my $user = "whatever"; my $password = "whatever"; my $dbh = DBI->connect("dbi:$db:dbname=$db_database", $user, $password); my $query = $dbh->prepare (q{SELECT arrival_date - INTERVAL '? MINUTE' FROM emails LIMIT 1}) or die ("unable to prepare"); $query->execute(60) or die("unable to execute"); print $query->fetchrow_array, "\n"; 

(arrival_name has this format: timestamp with time zone NOT NULL by default CURRENT_TIMESTAMP)

The problem is that the location of the question mark was not found, because its internal single quotes:

 DBD::Pg::st execute failed: called with 1 bind variables when 0 are needed 

This does not help if I use qq {}, $ 1 placeholder and tried several options with $ dbh-> quote. How can I do this job?

Answer 1

You cannot use placeholders inside quotation marks. You can use SQL string concatenation, but in this case it is easier to do this with multiplication:

 my $query = $dbh->prepare (q{SELECT arrival_date - ? * INTERVAL '1 MINUTE' FROM emails LIMIT 1}); $query->execute(60); 

This way you do not need to add ' minutes' to the number when executing the query.

Answer 2

I found a solution here: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=321917

It works using? :: interval instead of "interval?":

 my $query = $dbh->prepare (q{SELECT arrival_date - ?::interval FROM emails LIMIT 1}); $query->execute('60 MINUTE'); 

Answer 3

 my $query = $dbh->prepare (<<SQL) or die ("unable to prepare"); SELECT arrival_date - INTERVAL ? FROM emails LIMIT 1 SQL $query->execute("60 MINUTE"); 

<s> or

 my $query = $dbh->prepare (<<SQL) or die ("unable to prepare"); SELECT arrival_date - INTERVAL CONCAT(?, " MINUTE") FROM emails LIMIT 1 SQL $query->execute(60);