This really has nothing to do with Pg, and everything to do with SQL (and maybe DBI). I would avoid using the funky INSERT ... VALUES construct. Instead, you should name the columns that are to receive values, and associate the values you want to bind with the columns, via the insert into foo (a, b, c) values ('var_a', 'var_b) syntax. Something like:

my $sql1 = <<SQL_END;
insert into t ( tstamp,            message )
       values ( current_timestamp, ?       )
SQL_END

[download]

Creative use of whitespace lets you see at a glance which columns are bound to placeholders.

Secondly, as soon as placeholders come into play, don't use the do method. If it's a one-off, just interpolate the value of $var into the string, do that, and move on. (Just remember to quote $var beforehand ).

<update>I should elaborate on the above a bit more. Yes, using do+bind is useful, because it quotes (or defangs) the inputted values and thus guards you from SQL injection attacks. But you can always perform it yourself with the quote method. So you could execute the statement with:

  $db->do( 'insert into t values ( current_timestamp, '
    . $db->quote( $var1 )
    . ' )'
  ) or die "SQL go boom " . $db->errstr;

[download]

This is much less make-work code. Tip 'o the hat to Corion for nudging me to clarify this point.</update>

If you've gone to the trouble of setting up a placeholder, it's because you want to run the statement more than once. In that case, prepare the statement up front, and then execute it as necessary.

my $sth = $db->prepare( $sql1 ) or die(...);
...
$sth->execute( $var );

[download]

Finally, if you do wish to continue with the do/bind approach, you should note that the third parameter of do should be a reference to a hash. That's what both the docs and the error message are telling you. (Insert obligatory MJD maxim here).

update: Oops, no, I misread the docs. The third param is an array... but your error message talks about a hashref, which led me astray. Nevertheless, something weird is going on, so use the other INSERT form as suggested.

- another intruder with the mooring of the heat of the Perl

In reply to Re: DBD::Pg placeholder problem by grinder
in thread DBD::Pg placeholder problem by McMahon

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.