One option would be to check the output of a function from the Date::Manip
package, which could be used to check for a valid timestamp; if it's
undef(), return the original string; otherwise, return a timestamp. Then
you could write a custom quote() that would be sensitive to the function
vs. literal issue.
ap
----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu
On Wed, 29 Jan 2003, Nigel J. Andrews wrote:
>
> First off this is not really postgresql specific but it is driving me nuts.
>
> I thought I was using DBI to avoid the issues involved in constructing a SQL
> query string using values held in variables. It turns out I'm not I'm using it
> because it let's me write fetchrow_blah instead of some DB specific function
> that does the samething, like the nice simple API of Pg that no one likes to
> suggest people use.
>
> Anyway, back on to the subject. I'm a little stuck and I'm wondering how people
> handle the situation where a variable can contain a value _or_ a function
> call. For example:
>
> psql> create table mytab ( thetime timestamptz );
>
> perl:
> $sth = $dbh->prepare('insert into mytab values ( ? )');
> $sth->execute($thetime);
>
> where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
>
> Obviously these are just going to be normal string scalars in perl and DBI is
> just going to stick them in place as constant strings. Indeed it's difficult to
> see how it could do otherwise without going to great lengths. Even if it did,
> what then would it do if the column type was text? The trouble being is guess
> what happens when you do:
>
> insert into mytab values ('current_timestamp');
>
> Yep, it doesn't like trying to insert an incorrect timestamp representation
> into a timestamp field.
>
> So just how do others manage this situation without resorting to special casing
> everything?
>
>
> --
> Nigel J. Andrews
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>