如何使用SQL导出全文文件？

本文介绍了如何使用SQL导出全文文件？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有一种简单的方式将全文字段导入/导出为文件吗？

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

that solve the problem of "load as multiple lines".
Trying with SQL's COPY I can only to transform full-file into full-table, not into a single text field, because each line from COPY is a raw.

解决了保存问题，可以将完整的XML文件保存在文件系统中，而无需更改常规表示形式（保留SHA1 ），并且无需其他外部程序（如Unix sed 使用。）

that solve the save-back problem, to save the full XML file in the filesystem, without changes in bynary representation (preserving SHA1), and without other exernal procedures (as Unix sed use).

主要问题在于 export ，所以这是此页面的标题。

The main problem is on export, so this is the title of this page.

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

PS: the "proof of same file" in the the round trip — import, export back and compare with original — can be obtained by sha1sum demonstration; see examples below. So, a natural demand is also to check same SHA1 by SQL, avoiding to export on simple check tasks.

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

Import a full text into a full-table (is not what I need),
and test that can export as the same text.
PS: I need to import one file into one field and one row.

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

Transform full table into one file (is not what I need)
and test that can export as same text.
PS: I need one row (of one field) into one file.

通过SQL（字段的 SHA1 ）计算哈希。
比较时必须相同 ...否则这不是我的解决方案。

Calculate the hash by SQL, the SHA1 of the field.
Must be the same when compare ... Else it is not a solution for me.

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

The folowing examples show each problem and a non-elegant workaround.

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);

检查原始内容和后退内容是否完全相同：

Checking that original and "back" have exactly the same content:

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

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

PS: seems perfect, but the problem here is the use of many rows. A real import-solution can import a file into a one-row (and one field). A real export-solution is a SQL function that produce test_back.xml from a single row (of a single field).

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 ，对于 test_back2-bad.xml 不会产生相同的结果。

... But not works as we can check by sha1sum /tmp/test*.xml, not produce the same result for test_back2-bad.xml.

也可以使用外部工具（perl，sed或任何其他工具）将 \n 转换为chr（10）
perl -p -e's / \\n / \n / g'/tmp/test_back2-bad.xml> /tmp/test_back2-good.xml

So do also a translation from \n to chr(10), using an external tool (perl, sed or any other)
perl -p -e 's/\\n/\n/g' /tmp/test_back2-bad.xml > /tmp/test_back2-good.xml

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

Ok, now test_back2-good.xml have the same hash ("570b13fb..." in my example) tham original. Use of Perl is a workaround, how to do without it?

3。字段的SHA1

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

未解决，与原始哈希哈希值不同（在我的示例中为 570b13fb ... ）...也许 :: text 用 \n 符号强制执行内部表示，所以解决方案将是直接的强制转换为 bytea ，但这是无效的强制转换。另一个解决方法也不是解决方案，

Not solved, is not the same hash tham original (the "570b13fb..." in my example)... Perhaps the ::text enforced internal representation with \n symbols, so a solution will be direct cast to bytea, but it is an invalid cast. The other workaround also not is a solution,

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

...我尝试 CREATE TABLE btmp（x bytea）和 COPY btmp FROM'/tmp/test.xml'（ FORMAT binary），但出错（未知的COPY文件签名）。

... I try CREATE TABLE btmp (x bytea) and COPY btmp FROM '/tmp/test.xml' ( FORMAT binary ), but error ("unknown COPY file signature").

测试

/ tmp 是默认设置，用于创建或使用其他文件夹，例如。 / tmp / sandbox ，
使用 sudo chown postgres.postgres / tmp / sandbox 。

Testing

The /tmp is default, to create or use other folder, eg. /tmp/sandbox,
use sudo chown postgres.postgres /tmp/sandbox.

假设问题示例的表格。 SQL脚本，重复一些行：

Suppose the tables of the question's examples. SQL script, repeating some lines:

  DROP TABLE IF EXISTS ttmp; 
  DROP TABLE IF EXISTS xtmp; 

  CREATE TABLE ttmp (x text);
  COPY ttmp FROM '/tmp/sandbox/original.xml' ( FORMAT text );
  COPY (SELECT x FROM ttmp) TO '/tmp/sandbox/test1-good.xml' (format TEXT);

  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/sandbox/test2-bad.xml' ( FORMAT text );

  SELECT makefile('/tmp/sandbox/test3-good.xml',x::text) FROM xtmp;

我的 sha1sum * .xml 输出XML原始文件：

The sha1sum *.xml output of my XML original file:

4947..  original.xml
4947..  test1-good.xml
949f..  test2-bad.xml
4947..  test3-good.xml

这篇关于如何使用SQL导出全文文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

the