http://qs321.pair.com?node_id=513943

Ryszard has asked for the wisdom of the Perl Monks concerning the following question:

I'm having a bit of trouble with updating a date using bindvars.

Sounds simple i hear you say? Probably is, but i'll be buggered if i can find documentation on how. In a previous incantation of this problem i've done away with the bindvar and used a variable (shudder).

Basically i want something that looks like this: insert into table (ex_date) values (now()+"? minutes"::interval)

The unfortunate thing is postgres whinging with the error  DBD::Pg::st execute failed: called with 3 bind variables when 2 are needed

Various permutations of the above syntax i also cant get to work, and so, here I am, on my quest for a bit of perl (and postgres) wisdom

Replies are listed 'Best First'.
Re: OT: Postgres, now(), and bindvars
by cees (Curate) on Dec 04, 2005 at 13:26 UTC

    You can't put a ? inside a quoted string in your SQL statement as DBI won't see it as a placeholder, but as a simple string. I just pulled the following out of some existing code I wrote a while ago, so I know it works:

    INSERT INTO eventdates (eventdate, event, date) VALUES (?, ?, ?::timestamp + ?::interval)

    So what you need to do is add the ' minutes' part to a perl variable and pass that entire string to the placeholder.

Re: OT: Postgres, now(), and bindvars
by EvanCarroll (Chaplain) on Dec 04, 2005 at 21:53 UTC

    Two things to keep in the back of your mind:

    • Postgres allows functions to be used as defaults, such as CURRENT_TIMESTAMP, and CURRENT_DATE.
    • There is more than one way to cast something, cast('3 minutes' AS interval) and possibly even the old 7.x way of type(var) so interval('3 minutes'), though that should be discouraged.


    Evan Carroll
    www.EvanCarroll.com
Re: OT: Postgres, now(), and bindvars
by Ryszard (Priest) on Dec 07, 2005 at 11:36 UTC
    coolio, thanks for you're help, i'll try this stuff out when i get home.. :=)