Re: perl DBI and SQL COPY

I answered my own question (actually, the DBD::Pg email list did).
Something like this works:

SQL:
create table test1 (
    id  serial primary key,
    testdat varchar,
    testdat2 varchar);

perl:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:Pg:dbname=test;host=sherlock',
               '',
               '',
               {AutoCommit => 1},
              );

my $sql = qq{COPY test1 (testdat, testdat2) from STDIN};
my $sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;
foreach my $i (101..200) {
   my $line = "abc$i\tdef$i\n";
   my $ret = $dbh->func($line, 'putline');
   print $ret . "\n";
}
$dbh->func('endcopy');
$dbh->disconnect;


Sean


On Jan 4, 2005, at 5:52 AM, Sean Davis wrote:

> What is the accepted way (if there is one) of using the PostgreSQL
> COPY command in the setting of perl DBI.  I realize this is slightly
> off-topic, so feel free to send me elsewhere.
>
> Thanks,
> Sean
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings