如何使用 DBI 在 Postgres 中执行“\copy from remote table”命令？

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# 数据库连接参数
my $dsn = "DBI:Pg:dbname=mydb;host=localhost;port=5432";
my $username = "myusername";
my $password = "mypassword";

# 创建数据库连接
my $dbh = DBI->connect($dsn, $username, $password, {AutoCommit => 0})
    or die "Error connecting to database: $DBI::errstr";

# 准备 SQL 查询
my $query = "SELECT * FROM my_remote_table";

# 执行查询并将结果保存到本地文件
my $output_file = "output.csv";
my $copy_command = "COPY ($query) TO STDOUT WITH CSV HEADER";

open(my $fh, ">", $output_file) or die "Error opening output file: $!";
my $sth = $dbh->prepare($copy_command);
$sth->execute();
while (my $row = $sth->fetchrow_arrayref()) {
    print $fh join(",", @$row), "\n";
}
close($fh);

# 提交事务并关闭数据库连接
$dbh->commit();
$dbh->disconnect();