OT: Postgres, now(), and bindvars

[Ryszard]

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

[cees]

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.

[EvanCarroll]

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.

[Ryszard]

coolio, thanks for you're help, i'll try this stuff out when i get home.. :=)