perlygapes:

I wouldn't expect that to work: In order to prepare/compile the SQL statement, PostgreSQL is going to want to know the column names in advance. I'd suggest something a bit more like this:

# Given a list of column names build a placeholder list of the same le
+ngth
my @columns = ( 'customer_ID', 'reference1', 'foo', ... );
my $placeholders = join(", ", map { '?' } @columns);

# Build the SQL statement
my $SQL = "INSERT INTO myTable ("
        . join(", ", @columns)                # column names
        . ") VALUES ($placeholders)";
my $STH = $DBH->prepare_cached($SQL);

# Then if you have a reference to an array of values, use it like:
my $rValues = [ 'Customer_1', 'ref5', 1, ... ];
$STH->execute(@$rValues);

# Or you can hand it an array:
my @values = ( 'Customer_2', 'ref99', 3, ... );
$STH->execute(@values);

# or even just explicit values:
$STH->execute("Customer 5", "ref3", 7, ... );
[download]

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Reading through your example, I hear a ticking sound. I think I get what you're saying.
So...before any looping and generation of values to be INSERTed, I need to give Pg the list of column names when I first send the PREPARE to Pg?
I just looked at cpan again. It appears that in every example given, the prepare statement already has the column name(s). OK, I will try your suggestion.
Thanks.

Well, OK.
Your Pastoral Guidance has borne fruit and blessed my scriptural offerings.
I see smoke rising from the alter as I turn the STHandle on the door to data salvation.
But lo, are there wolves among these sheep?
Have I put my trust in a False Religion?

I receive no admonitions from Pg, yet upon SELECT supplications, it is as if my offerings are remembered no more. I wonder, are my indulgences counted as nothing more than lip service?

I accept, of course, the answer to this question may fall outside of the remit of the Perl Priesthood. Perhaps this is a question for the PG Priesthood?

Can Perl Priests intercede with Pg?
Doth thou knowest how to solicit exegetical corrective advice from Pg?

Must I make an open confession of faith with:
$sth->commit();
?

If I say $sth->errstr(); will Pg enlighten me?

Obligations

perlygapes:

I'd definitely try the commit to see if that does the trick, but I'd also check to see if the statement had an error or not. If it *does*, then you can use the AutoCommit flag on the connect statement to avoid having to do a commit after every operation (though you might prefer the safety of handling commit yourself).

Since you could be getting an error from Pg, you'll want to check the error status of any command. An insert will fail if you violate a constraint on the table for example. So I tell DBI (on the connect statement) to check every statement and raise errors (using RaiseError and PrintError).

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Just dereference your array refs.

-jo

Just to (maybe, hopefully) clarify: if $columns contains an array ref with your 22 column names and $values the 22 corresponding values you need to call $sth->execute( @{$columns}, @{$values} ) because it expects to get a flat list of parameters (not references to arrays with them). You're giving it the first below; it wants the second:

$sth->execute( [ "col1", "col2", ... ], [ "val1", "val2", ... ] );  ##
+ not working
$sth->execute( "col1", "col2", ..., "col22", "val1", "val2", ... ); ##
+ what it expects
[download]

The cake is a lie.
The cake is a lie.
The cake is a lie.

Oh the cake....yes, I know it's a lie, but it is just so tasty! :`(
Yes, $columns and $values are strings containing comma separated column names and column values I wish to insert into Pg.

I tried these, none of them work:

$sth->execute(@{$columns},@{$values});
$sth->execute(@columns,@values);
$sth->execute(\@columns,\@values);
[download]

I thought $sth->execute($columns,$values); would be closer to passing the flat comma separated string Pg needs.

What is just not clear in the DBD::Pg cpan documentation is how many arguments $sth->execute(); expects or can take, and I can't see a really clear/obvious example of where a variable is used. Most instances just show a hard-coded value...not very helpful.