pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedDBD-Pg Placeholder usage in v 1.49
2+ messages / 2 participants
[nested] [flat]
* DBD-Pg Placeholder usage in v 1.49
@ 2008-01-16 16:16 Max Pyziur <pyz@brama.com>
0 siblings, 1 reply; 2+ messages in thread
From: Max Pyziur @ 2008-01-16 16:16 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: DBD-Pg Placeholder usage in v 1.49
@ 2008-01-16 17:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Max Pyziur <pyz@brama.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2008-01-16 17:17 UTC (permalink / raw)
To: Max Pyziur <pyz@brama.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-01-16 17:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-16 16:16 DBD-Pg Placeholder usage in v 1.49 Max Pyziur <pyz@brama.com>
2008-01-16 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox