質問

PostgreSQLデータベースからPL/pgSQL出力をCSVファイルに保存する最も簡単な方法は何ですか？

PostgreSQL 8.4をpgAdmin IIIとPSQLプラグインで使用しており、ここからクエリを実行しています。

どのように解決するのですか？

作成したファイルはサーバーに置くのか、それともクライアントに置くのか？

サーバー側

再利用や自動化を簡単に行いたい場合は、Postgresqlに組み込まれた コピー コマンドを使用します。

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

この方法は、完全にリモート・サーバー上で実行されます - ローカルPCに書き込むことはできません。また、Postgresはそのマシンのローカルファイルシステムで厄介なことをするのを止めることができないため、Postgresのquot;スーパーユーザ（通常はquot;ルートと呼ばれます）として実行する必要があります。

だからといって、スーパーユーザーとして接続しなければならないわけではありません（それを自動化することは、別の種類のセキュリティリスクとなります）。 は SECURITY DEFINER オプションで CREATE FUNCTION という関数を作成します。 スーパーユーザーであるかのように実行する .

必要なデータを正確にエクスポートする関数を書くこともできますし、厳格なホワイトリストに適合する限り、さまざまなオプションを受け入れることができる関数を書くこともできます。あなたは2つのことをチェックする必要があります。

1. どれ ファイル は、ユーザーにディスク上の読み取り/書き込みを許可する必要がありますか？これは例えば、特定のディレクトリであったり、ファイル名に適切な接頭辞や拡張子が必要であったりする場合があります。
2. どの テーブル は、ユーザーがデータベースで読み取り/書き込みができるようにする必要がありますか？これは通常、以下のように定義されます。 GRANT しかし、この関数はスーパーユーザーとして実行されているため、通常は境界外であるはずのテーブルにも完全にアクセスすることができます。おそらく、誰かがあなたの関数を呼び出して "users" テーブルの末尾に行を追加するようなことはしたくないでしょう。

私が書いたのは このアプローチを発展させたブログ記事 また、厳しい条件を満たすファイルやテーブルをエクスポート（またはインポート）する関数の例も紹介します。

クライアント側

もう一つの方法は クライアント側でファイルを処理する つまり、アプリケーションやスクリプトの中です。Postgresサーバはコピー先のファイルを知る必要はなく、ただデータを吐き出し、クライアントがそれをどこかに置くだけです。

このための基礎となる構文は COPY TO STDOUT コマンドで、pgAdminのようなグラフィカルなツールは、それを素敵なダイアログで包んでくれます。

その psql コマンドラインクライアント という特別なメタコマンドを持っています。 \copy このオプションは、quot;real".とすべて同じです。 COPY しかし、クライアントの内部で実行されます。

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

終端がないことに注意してください。 ; なぜなら、メタコマンドはSQLコマンドとは異なり、改行で終了するからです。

から ドキュメント :

COPY と psql の命令である \copy を混同しないでください。\copyはCOPY FROM STDINまたはCOPY TO STDOUTを呼び出し、psqlクライアントがアクセス可能なファイルにデータを取り込み/格納します。このため、ファイルへのアクセスやアクセス権はサーバーではなく、クライアントに依存します。

使用するアプリケーションのプログラミング言語 かもしれません は、データのプッシュやフェッチをサポートしていますが、一般には COPY FROM STDIN / TO STDOUT を標準的な SQL 文の中で使用することはできません。これは、入出力ストリームを接続する方法がないためです。PHP の PostgreSQL ハンドラ ( ではなく PDO)には、非常に基本的な pg_copy_from と pg_copy_to 関数は、PHP の配列との間でコピーするもので、大きなデータセットでは効率的でないかもしれません。