posgresql \copyのfromに変数(csvのパス)を使いたい

Question

AWSのRDSでpostgresqlを使用しております。
定期的なデータ更新があり、シェルスクリプトとSQLで更新環境を作成しております。
ローカル(vagrantで作成したcentos6)で試していた際はsuperuser権限が使用出来たため、下記の様な書き方で実行しておりました。

\set :inputfile ←シェルから変数で渡しています。
copy hoge from :inputfile (format csv, header true);

ですがAWSのRDSだとsuperuserのロールが作れない、もしくは作るのが難しいため、下記のように書き変えました。
ですが「from :inputfile」の「:inputfile」の中のパスが展開の仕方が分からず困っております。
エラー内容も[:inputfile: No such file or directory]でおそらく「:inputfile」が展開されてないのではと思っております。※なぜか「末尾に:がついているのも気になりますが・・・」

\set :inputfile
\copy hoge from :inputfile with csv HEADER DELIMITER ',';

自分なりに調べた内容は以下の点です。
・ファイルのパスを直打ちで'hoge.csv'とすると正常に動作する。
・「/echo :inputfile」で出力されたパスは表示され catなどで確認しても中身はある。

もう少しで正常に動作させられそうなのですが、どうしてもfrom の部分の書き方が分からず質問致しました。
どなたか答え、もしくはヒントでも良いのでお助け下さい。

お礼と補足：
大変申し訳ございませんが、手元に実行環境が無いため検証できしだい、ベストアンサーを選択致します。
また、質問内容が分かりにくく申し訳ありません。

構成としては、「.sh」でCSV手前までのパスを定義して、そのファイル内でpsqlに接続し、一緒に実行する外部SQLファイル、変数を渡しています。

おそらく何通りも答えがありそうな予感がしております^^:
ベストアンサーはまだですが、先にみなさまアドバイスありがとうございました。

結果：
いくつかの方法を提示して頂けましたが、ファイルパス直打ちにしました。
アドバイス頂けたのに申し訳ないです。
中には私の理解不足で上手く試せてない方法もあります。

その他自分で試した方法として、一時テーブルを作成してサブクエリでfromを指定したりしてみましたが、ダメでした。

ベストアンサーを選んでからなので少し遅いですが、詳細な方法としては、下記の通りです。

#!/bin/bash

dbuser="hoge"
dbconection=`cat ./db_conection`
dbname=`cat ./db_name`
dbpassword=`cat db_password`
path=`pwd`"/import/"
datpath=$path"data/"

psql -h $dbconection -U $dbuser -d $dbname -f $path'sql/import.sql' -v datpath=$datpath

\set filename hoge.csv
\set inputfile '''':datpath:filename''''

\copy hoge from :inputfile with csv HEADER DELIMITER ',';
-- 上記の:inputfileがどうしても展開されず・・・

おそらくスクリプトの方を工夫することで問題の解決は図れそうですが、外部SQLの中が結構複雑で時間を考えて直打ちになりました。
せっかくアドバイス頂いたのに解決できずに申し訳ないです。

結果2
何度も申し訳ありません。
\copyコマンド自体をinputするという方法を上司に指南して頂きました。
スラッシュを2重にしないとダメなようでした。
ニッチすぎる情報ですが、誰かのお役に立てば。

\set copycmd '\copy hoge from ':inputfile' (format csv, header true);'
:copycmd

Answer 1

copyコマンドでもstdinで行えます

psql hogedb -c"copy tablename FROM stdin with csv HEADER DELIMITER ','" <hoge.csv

\copyで使えないオプションが使えます。
実はstdinとstdoutは\copyコマンドでも同様に可能です。

※なぜか「末尾に:がついているのも気になりますが・・・」

copyはpostgresサーバへのコマンドSQL文と同じなので、；が必要です。
\copyはpsqlクライアントへのコマンドなのでsetと同様に不要です。
copyはリモートサーバで実行され入力ファイルは、そのサーバで参照可能な場所になります。
stdin,stdoutはサーバがクライアントのpsqlに対しての要求になりクライアントの入出力が可能となります。

#!/bin/sh
inputfile=hoge.csv
psql hogedb -c"\copy hoge from '$inputfile' with csv HEADER DELIMITER ',';"

要望されているのと違いますがstdinを使った場合

#!/bin/sh
psql hogedb<<EOD
\set inputfile stdin
copy hoge from :inputfile with csv HEADER DELIMITER ',';
$(cat hoge.csv)
\.
EOD

Comments

[hpptms]

解答のお礼が遅くなってしまい申し訳ありません。

copyコマンドでもstdin、stdoutが使えるんですね。
私の質問の説明不足とサーバ、postgreの知識が足りていないため、理解出来ているか自身が持てませんが、SQL内でもstdin、stdoutが使えるんですね。

上記の質問をしたのが会社であったため、すぐに試せないのですが、
「:inputfile」をstdinに入力して、stdoutで文字列を出力してあげればいけそうな気がしてきました。
ご回答ありがとうございました。

[A.Ichi]

たぶん\set inputfile stdin とcopyコマンドはできるとおもいますが、その後にheaderとデータを入力する必要が有りますcsvで。さらに最終行に\.を入力して入力ファイル終了とします。

Answer 2

シェルスクリプトで変数を展開するにはこちらが参考になるかもしれません。
http://qiita.com/bsdhack/items/597eb7daee4a8b3276ba

次の書き方ではどうでしょう。

\copy hoge from ${:inputfile} with csv HEADER DELIMITER ',';

Comments

[hpptms]

解答のお礼が遅くなってしまい申し訳ありません。

ズバリ答えを教えていただきありがとうございます。
posgresqlについてまだ知識が浅く、SQL内で出来ることがまだまだ理解できていないです^^;
月曜出社したらすぐに試してみます。

Answer 3

書きました。
psql経由のCSV出力で任意のファイル名を渡す

copyで :'inputfile' のように、シングルクォーテーションで囲めば出来ると思います。
(\copyは出来ませんでした。)

Answer 4

こんにちは。
copyコマンドはDBのサーバ側のファイルシステムにファイルが置かれている（もしくは書き込みできる）のが前提なので、AWS RDSでは使えないという認識でいます。

そのため、¥copy コマンドを利用されたということですね。

シェルスクリプトとSQLで更新環境を作成

とありますが、shell (bash?) を使っているのでしたらpsqlの\setを経由しなくとも、bashの変数でファイル名を指定するのはだめでしょうか？
具体的にやりたいことがわかるといいのですが、推測の上でこんな作業でしょうか。

ワンライナーの例：

$ export tablename=foo
$ export inputfile=hoge.csv 
$ psql -d hoge -c "\copy $tablename FROM $inputfile with csv HEADER DELIMITER ','"
$ psql -d hoge -c "select * from $tablename"
 id | name 
----+------
  2 | var
  3 | test
  4 | tare
(3 rows)

作業用のディレクトリの *.csvを全部インポートしたいとかの場合（shell scriptを作成)

#!/bin/sh 
DBNAME=データベース名
TABLENAME=対象テーブル名
# ¥copyコマンドなので末尾の;は要らない
for INPUTFILE in *.csv 
do 
   psql $DBNAME -c "\copy $TABLENAME FROM $INPUTFILE with csv HEADER DELIMITER ','" 
done 

外していたら申し訳ありません....。

なお、

AWSのRDSだとsuperuserのロールが作れない、もしくは作るのが難しい

こちらについてですが、RDSのDBはデータベースのサービスだけを利用できるものなので、サーバ側のファイルシステムを利用する copy コマンドは利用できないと思います。（もし使うのであれば、標準出力でインポートする内容を転送するかたちで流し込むとか）

Comments

[hpptms]

解答のお礼が遅くなってしまい申し訳ありません。

シェルスクリプトはbashで書いております。
私の説明不足で申し訳ありませんが、hoge.sh内で変数を定義して、
そのhoge.sh内でpsqlをSQLファイルと定義した変数を渡して実行しております。

渡す変数の中身は[/home/hoge/]などのcsvファイルの手前までのパスです。
その変数とcsv名(こちらはSQLファイル内に直書き)を連携して、下記の様にしておりました。(手元にファイルが無いのと、前任者の方から引き継いだファイルを改造していたため、文法の間違いなどあるかもしれませんが、ご容赦ください)

\set csvfile 'human.csv'
\set targetfile '':get_sh_input:csvfile：csvfile''
↑上記を利用して「copy」から「\copy」に書き換えようとしていました。

質問をしたのが会社ですぐに試せませんが、何とかなりそうな気がしてきました。
またRDSについてのアドバイスも頂きありがとうございました。

[suama]

具体的に書いてくださってありがとうございます！
解決方法もできそうで何よりです。上の方が書いてくださっていましたが、こちらこそ色々変数の受け渡し方法があるんだと勉強させていただきました。