Re: Perl DBI and placeheld values - Mailing list pgsql-general

It occurs to me that you are sort of trying to bypass / defeat the purpose
of  "timestamp" -- I never try to create a home-grown timestamp -- Always
use the postgreSQL CURRENT_TIME and NOW() for postgreSQL timestamps...

ALSO: you probably already know but not all integers are integers -- There's
int2, int4, int4, bigint, tinyint, longint, integer, serial, auto_increment,
sequence, auto_number -- It depends on whose database you are using... The
same thing applies for all "data types"... floats, reals, strings...

As for "special casing" --  Who says Perl times are compatible with postgres
times... Almost every time is system dependent -- Therefore -- you can
either hard-code your perl scripts to match the OS  or comply with
postgreSQL's implementation (making them less portable) -- OR -- You can
have yourself an epiphany and rethink your database strategy...

your table might better be:

create sequence "mytable_seq";
create table mytable (

    id int4 default nextval "mytable_seq",
    thetime varchar(100) not null,
    create_dt timestamptz default 'NOW()'

);

$SQLSTMT = "INSERT INTO mytable (thetime) VALUES ('$thetime');";
$SQL = $DBH->prepare($SQLSTMT);
$result = $SQL->execute();


""Nigel J. Andrews"" <nandrews@investsystems.co.uk> wrote in message
news:Pine.LNX.4.21.0301292217590.2839-100000@ponder.fairway2k.co.uk...
>
> 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