Hi Jim,
Jim Stewart wrote:
> On Thu, 1 Mar 2007, Bart Samwel wrote:
>
>> I'm not experienced with prepared statements in postgresql, but given
>> the documentation the syntax "timestamp with time zone $1" does not look
>> correct to me. How about trying "$1::timestamp with time zone"?
>
> Bart,
>
> The syntax is correct, assuming $1 were to be replaced with a quoted value.
> Here's a working example, and the type of query I had been trying to
> generate with the prepared statement:
>
> select * from nac_targets where
> execution_time >= (timestamp with time zone '2007.001 UTC') and
> execution_time <
> ((timestamp with time zone '2007.001 UTC') + interval '3 days')
Ah, I think I see the subtlety here. The syntax "timestamp with time
zone 'x'" declares, AS A WHOLE, a single constant of type timestamp with
time zone, i.e., the whole construct is a unit and the language never
once considers 'x' a string. On the other hand, the syntax
"'x':timestamp with time zone" sees 'x' as a separate unit of type
string and then *casts* it to a timestamp using the :: casting operator.
As these units are separable, you can replace the 'x' with whatever you
want, including a bound parameter.
> I wasn't aware of the syntax you suggested, but it turns out that was the
> solution. The following SQL works:
>
> select * from nac_targets where
> execution_time >= '2007.001 UTC'::timestamp with time zone and
> execution_time < '2007.001 UTC'::timestamp with time zone +
> '3 days'::interval
>
> ..and its equivalent prepared statement:
>
> dbHandle->prepare("selectTargetsStmt",
> "select * from nac_targets where "
> "execution_time >= $1::timestamp with time zone and "
> "execution_time < $1::timestamp with time zone + $2::interval")
> ("timestamp", pqxx::prepare::treat_string)
> ("interval", pqxx::prepare::treat_string);
>
> It answers my other question, too; you can re-use a token like $1 and it does
> the right thing (replacing both instances) when invoked.
Cool!
Cheers,
Bart
_______________________________________________
Libpqxx-general mailing list
Libpqxx-general@gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
