Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

OT: Postgres, now(), and bindvars

by Ryszard (Priest)
on Dec 04, 2005 at 12:54 UTC ( #513943=perlquestion: print w/replies, xml ) Need Help??
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
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.. :=)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://513943]
Approved by g0n
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2017-02-24 00:29 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (351 votes). Check out past polls.