Обсуждение: DBD-Pg Placeholder usage in v 1.49

Greetings,

After having used DBD-PG-1.31 on Fedora Core 2 w/ perl 5.8.3 for a number 
of years, I've begun porting my work to
both Fedora 8 and Ubuntu where the DBD-Pg versions are 1.49. I've run into 
the seemingly often-visited placeholder issue.

Reviewing the README file, there is the following:
##############
To change your queries, make sure that the type of each placholder can
be determined by the PostgreSQL parser. So instead of:
  SELECT ?

use something like:
  SELECT ?::int

To turn off server-side prepares completely (with a loss of some 
performance and features), do this at the top of your scripts:

$dbh->{pg_server_prepare} = 0;

This can also be set for individual queries at the statment handle level: 
see the documentation for more details.

##############

I would like to follow the first recommendation rather than the first one. 
To that end, how would the following be modified, where the first 
placeholder is a text type, the second a date type, the third an integer 
type, and the fourth a date type.


FROM prices aa, oi_vol_tmp bb
WHERE aa.pr_date = bb.pr_date
AND symbol = ?
AND aa.pr_date BETWEEN date ? - integer ? AND ?

I've tried using:
AND aa.pr_date BETWEEN date ?::date - integer ?::int AND ?::date
with no success.

I've also tried looking for the appropriate documentation and haven't 
found any yet. I would appreciate suggestions as to where to look.


Max Pyziur
pyz@brama.com

Max Pyziur <pyz@brama.com> writes:
> FROM prices aa, oi_vol_tmp bb
> WHERE aa.pr_date = bb.pr_date
> AND symbol = ?
> AND aa.pr_date BETWEEN date ? - integer ? AND ?

> I've tried using:
> AND aa.pr_date BETWEEN date ?::date - integer ?::int AND ?::date
> with no success.

It should just be 

... AND aa.pr_date BETWEEN ?::date - ?::int AND ?::date

The type-name-at-the-front syntax is redundant with the cast, and
is only allowed for simple literal constants anyway.  It worked for
you before because DBD-PG was replacing the ? marks with literal
strings, but it wasn't ever really "the right thing".  See

http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
        regards, tom lane

Much thanks. That got me through the placeholder problem to bring up the 
next one. I now get a "cannot insert multiple commands into a prepared 
statement" error message.

The complete statement looks like this:
$PRICERPT1_SQL =   qq(SELECT pr_date, SUM(volume) AS tot_volume, 
SUM(open_interest) AS tot_oi
INTO TEMP oi_vol_tmp
FROM csi_prices
WHERE symbol LIKE ?
AND pr_date BETWEEN ?::date - ?::int AND ?::date
GROUP BY 1;

SELECT aa.pr_date, EXTRACT(dow FROM aa.pr_date) AS dow, EXTRACT(week FROM 
aa.pr_date) AS week,
EXTRACT(month FROM aa.pr_date) AS month, EXTRACT(year FROM aa.pr_date) AS 
year,
EXTRACT(doy FROM aa.pr_date) AS doy, EXTRACT(day FROM aa.pr_date) AS day,
aa.hi_price, aa.lo_price, aa.price, aa.volume, aa.open_interest, 
bb.tot_volume, bb.tot_oi
FROM prices aa, oi_vol_tmp bb
WHERE aa.pr_date = bb.pr_date
AND symbol = ?
AND aa.pr_date BETWEEN ?::date - ?::int AND ?::date
ORDER BY 1);

And it gets executed like this:
$dbh1 = DBI->connect($DSN_MAIN, $DBUSER, '', { AutoCommit => 1});
$dbh1->trace(0);

$PRICERPT1 = $dbh1->prepare($PRICERPT1_SQL); $PRICERPT1->execute($COM2, 
$ENDDATE, $INTERVAL, $ENDDATE, $COM, $ENDDATE, $INTERVAL, $ENDDATE);

while (@PRICERPT1 = $PRICERPT1->fetchrow_array) {

[...]

Is there any way to execute this in DBD-Pg 1.49? When I initially 
inserted:
$dbh->{pg_server_prepare} = 0;

the query worked as originally intended.

Thanks again.

Max Pyziur
pyz@brama.com



On Wed, 16 Jan 2008, Tom Lane wrote:

> Max Pyziur <pyz@brama.com> writes:
>> FROM prices aa, oi_vol_tmp bb
>> WHERE aa.pr_date = bb.pr_date
>> AND symbol = ?
>> AND aa.pr_date BETWEEN date ? - integer ? AND ?
>
>> I've tried using:
>> AND aa.pr_date BETWEEN date ?::date - integer ?::int AND ?::date
>> with no success.
>
> It should just be
>
> ... AND aa.pr_date BETWEEN ?::date - ?::int AND ?::date
>
> The type-name-at-the-front syntax is redundant with the cast, and
> is only allowed for simple literal constants anyway.  It worked for
> you before because DBD-PG was replacing the ? marks with literal
> strings, but it wasn't ever really "the right thing".  See
>
> http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
>
>             regards, tom lane
>