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
>