如何用SQL导出全文文件？

有将全文字段导入/导出为文件的简单方法？

• 解决“加载为多行”的问题。
  尝试使用 SQLCOPY我只能将完整文件转换为完整表格，而不能转换为单个文本字段，因为 COPY 中的每一行都是原始的。

• 解决了保存回问题，将完整的 XML 文件保存在文件系统中，而不改变二进制表示（保留 SHA1），并且没有其他外部过程（如 Unixsed use).

主要问题出在export，这就是本页的标题。

PS：往返过程中的“同文件证明”——导入、导出回来并与原始文件比较——可以通过以下方式获得sha1sum示范;请参阅下面的示例。因此，一个自然的需求也是通过 SQL 检查相同的 SHA1，避免在简单的检查任务上导出。

所有示例

1. 导入全文进入全表（不是我需要的），
   并测试可以导出为相同 text.
   PS：我需要将一个文件导入到一个字段和一行中。

2. 转换将整个表放入一个文件中（不是我需要的）
   并测试可以导出相同 text.
   PS：我需要将一行（一个字段）放入一个文件中。

3. 通过SQL计算hash，字段的 SHA1.
   Must be the same比较时...否则这对我来说不是一个解决方案。

以下示例显示了每个问题和一个不太优雅的解决方法。

1. 进口

CREATE TABLE ttmp (x text);
COPY ttmp FROM '/tmp/test.xml' ( FORMAT text ); -- breaking lines lines
COPY (SELECT x FROM ttmp) TO '/tmp/test_back.xml' (format TEXT);

检查原件和“背面”的内容是否完全相同：

sha1sum /tmp/test*.*
  570b13fb01d38e04ebf7ac1f73dfad0e1d02b027  /tmp/test_back.xml
  570b13fb01d38e04ebf7ac1f73dfad0e1d02b027  /tmp/test.xml

PS：看起来很完美，但这里的问题是使用了很多行。真正的导入解决方案可以将文件导入一行（和一个字段）。真正的导出解决方案是一个 SQL 函数，它生成test_back.xml来自（单个字段的）单行。

2. 将全表转换为一个文件

用它来存储 XML：

CREATE TABLE xtmp (x xml);
INSERT INTO  xtmp (x) 
  SELECT array_to_string(array_agg(x),E'\n')::xml FROM ttmp
;
COPY (select x::text from xtmp) TO '/tmp/test_back2-bad.xml' ( FORMAT text );

...但不起作用，因为我们可以检查sha1sum /tmp/test*.xml, 不会产生相同的结果 for test_back2-bad.xml.

所以也翻译一下\n到 chr(10)，使用外部工具（perl、sed 或任何其他）
perl -p -e 's/\\n/\n/g' /tmp/test_back2-bad.xml > /tmp/test_back2-good.xml

Ok, now test_back2-good.xml具有与原始相同的哈希值（在我的示例中为“570b13fb ...”）。 使用 Perl 是一种解决方法，没有它怎么办？

3. 字段的SHA1

SELECT encode(digest(x::text::bytea, 'sha1'), 'hex') FROM xtmp;

未解决，与原始哈希值不一样（我的示例中的“570b13fb ...”）...也许::text强制内部代表\n符号，因此解决方案将直接转换为bytea，但这是一个无效的强制转换。其他解决方法也不是解决方案，

SELECT encode(digest( replace(x::text,'\n',E'\n')::bytea, 'sha1' ), 'hex') 
FROM xtmp

...我尝试CREATE TABLE btmp (x bytea) and COPY btmp FROM '/tmp/test.xml' ( FORMAT binary )，但出现错误（“未知的复制文件签名”）。

COPY不是为此设计的。它旨在处理表结构数据，因此如果没有某种划分行和列的方法，它就无法工作；总会有一些角色COPY FROM解释为分隔符，并且为此COPY TO如果在您的数据中找到转义序列，则会插入一些转义序列。如果您正在寻找通用文件 I/O 工具，这不太好。

事实上，数据库服务器并不是为一般文件 I/O 设计的。一方面，anything直接与服务器的文件系统交互将需要超级用户角色。如果可能的话，您应该像往常一样查询表，并在客户端处理文件 I/O。

也就是说，有几种选择：

• 内置的pg_read_file()函数，以及pg_file_write()来自adminpack模块，提供与文件系统最直接的接口，但它们都仅限于集群的数据目录（我不建议在其中存储随机的用户创建的文件）。
• lo_import() and lo_export()是我所知道的唯一直接处理文件 I/O 的内置函数，并且可以不受限制地访问服务器的文件系统（在主机操作系统施加的限制内），但是大对象接口并不是特别用户友好....
• 如果您安装像 Perl 这样的过程语言的不受信任变体（plperlu) 或 Python (plpythonu），您可以为该语言的本机 I/O 例程编写包装函数。
• 没有什么是你无法完成的COPY TO PROGRAM如果你有足够的决心 - 对于一个人来说，你可以COPY (SELECT 1) TO PROGRAM 'mv <source_file> <target_file>'解决以下限制pg_file_write()- 尽管这在某种程度上模糊了 SQL 和外部工具之间的界限（无论继承你的代码库的人可能不会印象深刻......）。