将 PL/pgSQL 输出从 PostgreSQL 保存到 CSV

1. 在 PostgreSQL 中使用 COPY 命令以 CSV 格式保存输出
2. 使用 > 的控制台规则
3. 有助于将数据从 PostgreSQL 保存到 CSV 文件的 PSQL2CV 工具

在我们开始着手解决这个问题的不同解决方案之前，让我们了解一下 CSV 文件是什么以及 PL/pgSQL 中的输出是如何工作的。

PL/pgSQL 输出是在 PostgreSQL 中运行特定类型的查询后显示的数据。有时，由于运行查询或更大的查询，你可能需要将显示的数据存储在电子表格中，同时保持其简单以便以后查看或共享。

这就是 CSV 的用武之地。CSV 文件是一个 COMMA SEPARATED VALUES 文本文件。

倾向于用逗号分隔值的东西，主要在电子表格中找到。这是用户可以用来读取和写入数据的最简单的数据存储形式。

现在让我们看看如何有效地将输出存储在 CSV 文件中。

在 PostgreSQL 中使用 COPY 命令以 CSV 格式保存输出

在 PostgreSQL 文档中，COPY 命令被列为在文件和表之间复制数据的东西。

COPY 命令使用以下语法。

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

在这里，你可以看到我们如何将数据从表中保存到文件中。我们将通过一个实际示例让你学习如何更好地使用它。

COPY 关键字有两个版本；COPY TO 和 COPY FROM。

正如你从名称中所知道的那样，COPY TO 将数据从表中复制到指定的文件中。另一方面，COPY FROM 将数据从文件复制到表中。

如果指定了列，我们文件中的每个字段都将插入到该特定列中。如果没有，该列将从提供的文件中接收默认值。

在另一种情况下，你甚至可以使用 PROGRAM 语法代替文件，然后从该 PROGRAM 的 OUTPUT 或 WRITE 读取该程序的 INPUT。在 STDIN 或 STDOUT 的情况下，数据通过客户端-服务器交互传输。

现在让我们继续使用 COPY TO 命令将我们的输出保存为 CSV。例如，今天，我们已经创建了一个名为 CAT 的表，该表如下所示。

id      name
1	"Adam"
2	"Jake"

现在让我们继续尝试运行 COPY TO 查询。

COPY (Select * from CAT) TO 'D:/test.csv'

我们在这里使用了驱动器 D:，因为它往往会给出最少的权限错误。如果你继续访问此文件，你将看到如下内容。

输出：

等等不好。发生了什么？

不幸的是，当我们尝试运行 COPY TO 操作时，我们的查询成功运行。然而，结果并没有被分隔，因为它们应该使用 COMMA 分隔符。

他们最终被附加在一起。那么我们如何防止这种情况呢？

仔细查看我们的语法，你会注意到 COPY TO 子句中的 OPTION 设置。你可以在此 OPTION 中添加 COMMA 分隔符来分隔你的结果。

OPTION 可以是以下任何一种。

FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

我们将在这里使用 DELIMITER 关键字。所以现在，我们可以如下修改我们的查询。

copy (select * from cat) to 'D:/test.csv' with delimiter ','

如果你现在运行查询，结果将整齐地格式化为：

输出：

但是，它还缺少一件事：标题。我们的列名应该和我们的数据一起出现。

我们还可以使用可能的 OPTION 关键字中的 HEADER 选项。PostgreSQL 列出了 HEADER 的使用如下。

Specifies that the file contains a header line with the names of each column in the file. The first line contains the column names from the table on output, and on input, the first line is ignored. This option is only allowed when using CSV format.

请记住仅在 CSV 格式中使用 HEADER。你现在可以使用如下查询。

copy (select * from cat) to 'D:/test.csv' with delimiter ',' HEADER

但是我们得到如下错误。

输出：

ERROR:  COPY HEADER available only in CSV mode
SQL state: 0A000

发生此错误是因为我们的 PostgreSQL 会话仍然不知道我们的文件是否为 CSV 格式。在文件名中使用 .CSV 扩展名有效，但不是唯一读取以确定我们的文件是否为 CSV。

因此，我们可以使用如下查询在 CSV 中定义我们的文件模式：

copy (select * from cat) to 'D:/test.csv' with delimiter ',' HEADER  CSV

我们的查询现在返回完美的结果。

输出：

PostgreSQL 中的客户端与服务器端数据保存

让我们想象一个 PostgreSQL 会话； SES.1 在所有其他计算机都连接到的 COMPUTER 2 上运行。其他计算机，简称为客户端，拥有从 COMPUTER 3 到 COMPUTER 11 的一系列计算机； COMPUTER 2 是服务器。

现在 COMPUTER 4 想要从 SES.1 下载数据。但是在它的计算机上，它会向 SES.1 发送 COPY TO 查询并等待下载。

但是，尽管下载了文件，COMPUTER 4 却永远无法在其目录中找到该文件。为什么？

当 COMPUTER 4 对 SES.1 运行 COPY TO 查询时，SES.1 不下载文件。相反，它会引发 USAGE ACCESS 错误。为什么？

我们的 COMPUTER 4 不是超级用户。它只是另一台刚刚连接用于发出简单 SQL 查询的 PC。

我们的 PostgreSQL DBMS 确保有底层的安全协议不能使这项工作。

DBMS 负责确保防止对主服务器的攻击。如此多的用户没有被授予 SUPERUSER 访问权限，这是 COPY FROM 操作从连接到服务器的不安全客户端成功的条件。

来自 PostgreSQL 文档的摘要使这个问题浮出水面。

另一个可能会给用户访问服务器带来问题的非常重要的约束是查询 PRIVILEGES (SELECT, INSERT) 和 ROW-LEVEL 安全性可能会阻止用户访问服务器以某种方式从不允许特定操作成功的表。

EXECUTE QUERY TO FILE 是另一个查询，我们可以使用它来将导出的数据写入磁盘上的文件。在这种情况下，你不必专门使用 COPY TO STDOUT 查询来使其工作。

\COPY 命令导出数据

PSQL 带有一个名为 \COPY 的命令。它可能看起来类似于 COPY TO，但事实并非如此。

如果你使用 \COPY 下载文件，它将首先调用 COPY TO STDOUT 函数，然后确保将此数据获取到一个文件中，该文件很可能是我们的客户端可以访问的文件。

因此，如果你查看它，则无需拥有 SUPERUSER 访问权限即可从服务器使用 COPY TO 或 FROM 命令。 \COPY 倾向于解决许多问题。

为了将它用于我们上面阐述的相同示例，让我们继续打开 PSQL，然后编写如下内容。

\copy (SELECT * from CAT) to D:\lopster.csv

成功后将返回如下输出。

COPY 2

因此，\COPY 似乎是 PostgreSQL 中传统使用 COPY TO 和 FROM 的更好替代方案。

在大多数情况下，如果每个用户尝试访问 SERVER 并发出下载命令，我们可能会避免以 ROOT 身份连接到 SERVER 或为每个用户分配 SUPERUSER 权限。

其中最突出的一件事是 SECURITY DEFINER 子句，它倾向于绕过用户 PRIVILEGES 问题并让用户创建一个他们可以在大多数情况下用作 SUPERUSER 的功能。

它在 PostgreSQL 文档中指定如下。

| { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }

SECURITY DEFINER 以创建它的用户的权限执行。在我们的服务器上，ROOT 将始终创建函数。

因此，在其上放置一个 DEFINER 将让其他用户将该函数称为 ROOT。这往往会阻止两个最常见的问题。

1. SUPERUSER 权限的泛化。
2. 连接用户的批量权限设置。

当然，SECURITY DEFINER 能够安全地将 EXECUTE 权限授予它可以信任的少数客户端或用户，主要是 ADMINS。在这种情况下，它将使用 REVOKE 或 GRANT 调用来确保此类权限仅授予可信赖的用户。

我们稍后将学习的对 CREATE FUNCTION 查询的简单修改是这样的：

GRANT EXECUTE ON FUNCTION test_func() TO admins;

现在让我们定义这个 TEST_FUNC() 并看看我们如何使用它。

CREATE FUNCTION test_func()
	RETURNS VOID
	SECURITY DEFINER
	LANGUAGE SQL
	AS $BODY$
		copy (select * from cat) to 'D:/test.csv' with delimiter ',' HEADER  CSV;
	$BODY$;

在这里，我们创建了一个函数，将安全性定义为 ROOT 用户来访问它，而 BODY 包含 COPY 方法，现在遇到了不同的 ROOT 访问权限。

在创建一个允许 SUPERUSER 访问服务器以发出 SQL 查询的函数时，我们必须确保一些事情。

1. 将 SQL 字符串作为参数传递给函数。
2. 向 SYSTEM 上的每个用户颁发 EXECUTE 权限。

你可以有效地确保上述情况根本不会发生。对于第一部分，只需让 FILENAME 和 TABLENAME 参数传递给函数即可使其工作。

在这种情况下，用户将能够从我们的函数中未静态定义的表中下载数据。但是，例如，使用 SQL 字符串允许用户发出查询，你可以在其中从表中选择或在表（动态）导出中定义其他条件，这可能更具破坏性。

像这样的查询将如下所示。

CREATE FUNCTION test_func(SQL_Query TEXT)
	RETURNS VOID
	SECURITY DEFINER
	LANGUAGE SQL
	AS $BODY$
		EXECUTE SQL_Query;
	$BODY$;

现在任何传递给它的 SQL_Query 都将作为 SUPERUSER 执行，问题来了；如果传递了一个完全不同的查询的 STRING，我们该怎么办，例如； "SELECT * from pg_admin"，一些旨在打开 PostgreSQL DBMS 后门的攻击。

因此，在这种情况下，该命令将作为 SUPERUSER 执行，我们的连接将不安全。

对于第二部分，访问 FUNCTIONS 的权限也可以限制为值得信赖的用户，然后他们可以继续使用 SUPERUSER 权限执行查询，而不会产生任何问题。

其中一些查询可以分配给 GROUP_ROLES，最多为 ADMINS 或服务器内受信任的其他组，不会成为安全漏洞的一部分。

在这种情况下，我们可以使用 IF 和 ELSE 语句通过确保不允许执行无效文件名来防止 SQL 注入。查询遵循我们方法中的特定类型。

在这种情况下，只允许执行少数查询。一个例子可能如下。

CREATE FUNCTION test_func(file_s text, table_s text)
	RETURNS VOID
	LANGUAGE plpgsql
	SECURITY DEFINER
	AS $BODY$
	DECLARE
		file_path text := '/var/my_application/csv_output/';
		file_name_regex text := E'^[a-zA-Z0-9_-]+\\.csv$';
		table_name_regex text := '^temp_export_[a-z_]+$';
	BEGIN
		IF
			table_s !~ table_name_regex
		THEN
			raise exception 'Invalid';
		END IF;

		IF
			file_s !~ file_name_regex
		THEN
			raise exception 'File name Invalid!';
		END IF;

		EXECUTE '
			COPY
				' || quote_ident(table_name) || '
			TO
				' || quote_literal(file_path || file_name) || '
			WITH (
				FORMAT CSV, HEADER
			);
		';
	END;
$BODY$;

REVOKE ALL ON FUNCTION test_func(file_s text, table_s text)
	FROM PUBLIC;
GRANT EXECUTE ON FUNCTION test_func(file_s text, table_s text)
	TO [GROUP_NAME];

这里我们使用 REGEX 来定义可以执行的特定查询语法。它可能没有无效字符，如果我们传递的参数不等于它，它们被描述为 INVALID。

Rest 是将函数中的 PARAMETER 名称附加到 EXECUTE 查询，然后 ENDING 该函数。

REVOKE ALL 将删除 PUBLIC 组的用户权限，该组可能是系统中的所有用户。一旦它被撤销并且公共组不能再访问我们的方法，我们就可以指定可以承担这个角色的 GROUP_Name 和 EXECUTE 我们的方法。

使用 > 的控制台规则

你可能已经在命令行中看到了符号'>'的使用。它指定将前面的参数的输出放在后面的参数或符号之后传递的名称。

因此，可以应用如下语法。

[QUERY] > [FILE_NAME]

在 PSQL 中，要从文件中导出数据，我们可以执行以下操作。

psql -U postgres -d postgres -t -A -F"," -c "select * from cat" > D:\test_2.csv

不指定 -U 命令往往会以普通用户的身份发出连接，在大多数情况下，这可能无法正常工作。其他参数包括在 -C 之后传递的表，它代表 Command，然后是 OUTPUT 到 FILENAME，目录在 FILE_NAME 参数中定义。

在此之前，你可能会注意到 -T、-A 和 -F 关键字。它们是什么，我们如何使用它们？

-T 是在输出 CSV 文件中不打印 COLUMN_NAMES 的关键字。如果你要删除 -T 然后发出命令，则差异如下，除了 COLUMN_NAMES 和 ROW_FOOTERS。

使用 -T：

输出：

1	Adam
2	Jake

没有 -T：

输出：

id	name
1	Adam
2	Jake
(2 rows)

-A 将 NON-EMPTY 输入行打印到我们控制台的标准输出。而 -F 代表 PSQL 中的 SEPARATORS，我们将分隔符定义为 ,，它可以用于我们的文件。

正如你可能已经猜到的那样，不使用 -F 会导致如下情况：

输出：

id|name
1|Adam
2|Jake
(2 rows)

现在我们已经清楚在 PSQL 中使用 '>' 命令了，让我们看看更多将 PostgreSQL 数据保存到我们的文件中的方法。

PSQL 查询修改以保存系统中表或表达式中的数据

发出上面给出的 PSQL 命令的另一种方法是使用如下内容。

postgres=# \f ','
Field separator is ",".
postgres=# \a
Output format is unaligned.
postgres=# \o 'D:/table_make.csv'
postgres=# select * from cat;
postgres=# \q

一旦连接到 PostgreSQL 会话，就可以使用它。你定义字段分隔符和输出格式，然后发出附加到 SELECT 查询的\O 命令。

所以你在 #6 和 7 行中所做的可以写成：

\o 'D:/table_make.csv' select * from cat;

-O 或 \O 将所有查询输出放入定义的文件名中。它的语法如下。

--output=filename

\O 命令的另一个很好的替代方法是\G，它允许你通过保存 Postgres 表中的数据来定义选项。它的使用如下。

\g [ (option=value [...]) ] [ filename ]
\g [ (option=value [...]) ] [ |command ]

在 PostgreSQL 中将数据保存到 CSV 文件的不同接口上发出命令的基本结构

在诸如 SSH 之类的东西上，以下将起作用。

$ ssh [PG_SERVER] 'psql -d postgres "COPY (select * from cat) TO STDOUT WITH CSV HEADER"' > output_make.csv

对于 Docker，我们可以使用以下内容。

$ ssh [PG_SERVER] 'docker exec -tu postgres postgres psql -d postgres "COPY (select * from cat) TO STDOUT WITH CSV HEADER"' > output_make.csv

在 KUBERNETES CLUSTER 中，往往是运行具有打包依赖项的应用程序的节点，我们可以使用：

kubectl exec -t postgres-2592991581-ws2td 'psql -d postgres -c "COPY (select * from cat) TO STDOUT WITH CSV HEADER"' > output_make.csv

在 PSQL 中发出的另一组命令包括 FORMAT CSV 选项的不同变体。再次澄清，我们提供了不同的变体，以允许我们的读者有多个选项来运行命令，而不仅仅是继续执行一个命令，以防万一这不起作用，最终出现错误。

psql -U postgres -c "SELECT* FROM cat" --csv postgres

这往往会以带逗号的 CSV 格式获取表格，然后将其输出到控制台。

输出：

 id,name
1,Adam
2,Jake

可能遵循的另一个实现是：

psql -U postgres -c "SELECT * FROM cat" --csv -P csv_fieldsep="^"  postgres

这将使用^而不是 , 来分隔值。但是，如果你倾向于错误地将 FIELD_SEP 值放在单个逗号中，则会返回如下错误：

psql: error: \pset: csv_fieldsep must be a single one-byte character
psql: fatal: could not set printing parameter "csv_fieldsep"

因此，请确保将其括在双逗号中，以确保它是一个 BYTE 字符串而不是单个 CHAR。运行上述将输出如下内容。

输出：

id^name
1^Adam
2^Jake

然后从这里，我们可以使用 PIPELINING 将我们的数据有效地输出到系统中。

psql -U postgres -c "SELECT* FROM cat" --csv postgres > D:\point_table.csv

在这样的场景中，我们最好使用'或"符号来表示 FILE_NAMES 或其他重要关键字。最好交替使用它们以充分利用两者，看看哪个有效完美。

另一个与上述类似的语法包括：

psql -h [DB_LOC] -p [PORT] -U user -d [DB_NAME] -F $'\t' --no-align -c "SELECT * FROM CAT" > import-tab.csv

有助于将数据从 PostgreSQL 保存到 CSV 文件的 PSQL2CV 工具

要在 Homebrew 上安装 PSQL2CSV，你可以使用以下内容。

$ brew install psql2csv

你可能必须使用 CHMOD 进行手动安装，才能使用可执行文件进行完整安装。

PSQL2CSV 遵循以下语法：

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

要使用该命令，请使用以下命令。

psql2csv dbname "select * from cat" > data.csv

或者

$ psql2csv --no-header --delimiter=$',' --encoding=latin1 [DB_NAME] <<sql
> SELECT *
> FROM cat
> LIMIT 1
> SQL

这将只打印一行，没有标题，分隔符定义为 ,。

我们希望你学习了使用我们今天学习的许多不同方法将数据从 PostgreSQL 数据库输出到 CSV 文件的不同方法。遵循正确的编码符号，如果你在给定的代码中发现错误，请联系我们。