sql - 使用 perl/SQL 将文件插入 Postgres bytea 列

Question

我正在使用遗留系统，需要找到一种使用 Perl 将文件插入到预先存在的 Postgres 8.2 bytea 列中的方法。

到目前为止，我的搜索使我相信以下内容:

1. 对此没有达成共识的最佳方法。
2. lo_import 看起来很有前途，但我显然太迟钝了，无法让它发挥作用。

我希望做类似下面的事情

my $bind1 = "foo"
my $bind2 = "123"
my $file = "/path/to/file.ext"

my $q = q{
      INSERT INTO generic_file_table
        (column_1,
         column_2,
         bytea_column
        ) 
      VALUES
      (?, ?, lo_import(?))
};
my $sth = $dbh->prepare($q);
$sth->execute($bind1, $bind2, $file);
$sth->finish();`

我的脚本没有 lo_import/bytea 部分。但是有了它我得到了这个错误:

DBD::Pg::st execute failed: ERROR: column "contents" is of type bytea but expression is >of type oid at character 176 HINT: You will need to rewrite or cast the expression.

我认为我做错的是我没有正确地将实际的二进制文件传递给数据库。我想我传递的是文件路径，而不是文件本身。如果这是真的，那么我需要弄清楚的是如何将文件打开/读入 tmp 缓冲区，然后使用该缓冲区进行导入。

还是我离基地太远了？只要它们适用于 Perl 5.8/DBI/PG 8.2，我愿意接受任何指示或替代解决方案。

Answer 1

Pg 提供了两种存储二进制文件的方式:

• 大对象，在 pg_largeobject 表中，由 oid 引用。通常通过 lo 扩展使用。可以用 lo_import 加载。

常规表中的

• bytea 列。在 PostgreSQL 9.0 及以下版本中表示为八进制转义符，如 \000\001\002fred\004，或在 Pg 9.1 及更高版本中默认为十六进制转义符，例如 \x0102。 bytea_output 设置允许您在具有 hex 格式的版本中选择 escape(八进制)和 hex 格式。

您正在尝试使用 lo_import 将数据加载到 bytea 列中。那是行不通的。

您需要做的是向 PostgreSQL 发送正确转义的 bytea 数据。在受支持的当前 PostgreSQL 版本中，您只需将其格式化为十六进制，在前面敲一个 \x 即可。在您的版本中，您必须将其转义为八进制反斜杠序列，并且(因为您使用的是不使用 standard_conforming_strings 的旧 PostgreSQL)可能也必须将反斜杠加倍。

This mailing list post 提供了一个很好的例子，可以在你的版本上工作，后续消息甚至解释了如何修复它以在不太史前的 PostgreSQL 版本上工作。它展示了如何使用参数绑定(bind)来强制 bytea 引用。

基本上，您需要读取文件数据。您不能只将文件名作为参数传递 - 数据库 服务器 将如何访问本地文件并读取它？它会在服务器上寻找路径。

读入数据后，需要将其转义为 bytea 并将其作为参数发送到服务器。

更新:像这样:

use strict;
use warnings;
use 5.16.3;

use DBI;
use DBD::Pg;
use DBD::Pg qw(:pg_types);
use File::Slurp;

die("Usage: $0 filename") unless defined($ARGV[0]);
die("File $ARGV[0] doesn't exist") unless (-e $ARGV[0]);
my $filename = $ARGV[0];


my $dbh = DBI->connect("dbi:Pg:dbname=regress","","", {AutoCommit=>0});
$dbh->do(q{
        DROP TABLE IF EXISTS byteatest;
        CREATE TABLE byteatest( blah bytea not null );
});
$dbh->commit();

my $filedata = read_file($filename);
my $sth = $dbh->prepare("INSERT INTO byteatest(blah) VALUES (?)");
# Note the need to specify bytea type. Otherwise the text won't be escaped,
# it'll be sent assuming it's text in client_encoding, so NULLs will cause the
# string to be truncated.  If it isn't valid utf-8 you'll get an error. If it
# is, it might not be stored how you want.
#
# So specify {pg_type => DBD::Pg::PG_BYTEA} .
#

$sth->bind_param(1, $filedata, { pg_type => DBD::Pg::PG_BYTEA });
$sth->execute();
undef $filedata;

$dbh->commit();