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