將 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 檔案的不同方法。遵循正確的編碼符號，如果你在給定的程式碼中發現錯誤，請聯絡我們。