shellからpostgresqlへの変数の引き渡しができない

test.shというshellファイルからtest.sqlというpostgresqlファイルに変数の引き渡しをしたいのですができません。
test.shというファイルはcsvファイルを1行ずつ読み込み、ip_tmpにipアドレス、os_tmpにそのipアドレスのos名を取得し表示するものです。
その後、test.sqlファイルにDATEコマンドで得た値、ip_tmp、os_tmpの中身を引き渡したいです。

変数の引き渡し方を調べたところ-vオプションで可能だと知りました。
sqlファイルで使用する際は変数の前に:をつけるとのことでした。

<test.sh>
while read row; do
ip_tmp=$(echo ${row} | cut -d , -f 3)
os_tmp=$(echo ${row} | cut -d , -f 4)

echo "${ip_tmp}のOSは${os_tmp}です。"

/usr/local/bin/psql -U nakakukitatsuya -d nmapresult < test.sql -v "col=$DATE,os_tmp=$os_tmp,ip_tmp =$ip_tmp"
done < /Users/nakakukitatsuya/Downloads/p0f-master/2019-12-13-13:45:01.csv

<test.sql>
do $$ begin
if not exists (select * from ostable where os=':os_tmp') then
insert into ostable (ip,os,"':col'") values (":ip_tmp",":os_tmp",1);
else
update ostable set ":col" = 1;
end if;
end$$;

delete from ostable where os='???';

<ostable>
ip | os | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23
----+----+---+---+---+---+---+---+---+---+---+---+----+----+----+----+----+----+----+----+----+----+----+----+----+----

＜エラーメッセージ＞
163.226.156.149のOSはMac OS Xです。
psql: warning: extra command-line argument "OS" ignored
psql: warning: extra command-line argument "X,ip_tmp" ignored
psql: warning: extra command-line argument "=163.226.156.149" ignored
ERROR: column "':col'" of relation "ostable" does not exist
LINE 1: insert into ostable (ip,os,"':col'") values (":ip_tmp",":os_...
^
QUERY: insert into ostable (ip,os,"':col'") values (":ip_tmp",":os_tmp",1)
CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement
DELETE 0

質問者からの補足コメント

• insert into ostable (ip,os,:col) values (:ip_tmp,:os_tmp,1);
  
  test.sqlを少し変更し、そもそもinsert文、delete文以外をコメントアウトし実行したところ下記のエラーが出ました。
  
  
  185.216.140.34のOSは???です。
  ERROR: syntax error at or near "14"
  LINE 1: insert into ostable (ip,os,14) values (185.216.140.34,???,1)...
  ^
  DELETE 0
  
  解決方法もしありましたらご教授いただけると嬉しいです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/01/08 15:54

• 何度も申し訳ありません。
  insert into ostable (ip, os, :"col") values (:'ip_tmp', :'os_tmp', 1);
  このように:を"や'の前に出すことでinsertはうまく動きました。
  
  do $$ begin
  if not exists (select * from ostable where os=:'os_tmp') then
  insert into ostable (ip, os, :"col") values (:'ip_tmp', :'os_tmp', 1);
  else
  update ostable set :"col"=1;
  end if;
  end$$;
  
  しかし、コメントアウトしていたところを元に戻すと2行目のwhere os=:でsyntax error at or near ":"とでました

    補足日時：2020/01/08 16:46

No.1 ベストアンサー 回答者： kmee 回答日時： 2020/01/06 20:00

結論から言うと、 -v で指定できるのは変数1つだけで、複数設定するなら -v を複数使う必要があります。

まず、シェルスクリプトで意図したオプションになっているかを確認しましょう。
先頭に set -x -v を入れるとか、 sh -vx と指定するとかで、実行時にどんなコマンドを実行しているのか、変数等を展開してどうなっているのか、等が出力されるようになります。

おそらく、あなたが今意図しているコマンドになっているでしょう。


https://www.postgresql.jp/document/11/html/app-p …
-v オプションは \setメタコマンドと同等と書いてあります。
\set を読むと、引数無しで実行すると、全ての変数が出力される、とあります。

test.sql の代りに、 \set だけのSQLファイルを用意して実行すれば、実際にどんな変数が定義されたかを確認することができます。
そうすると os_tmp,ip_tmpが出きているわけではなく
col=日付,os_tmp=Mac OS X,ip_tmp =163.226.156.149
と変数colだけが定義されている、ということがわかるでしょう。
と
※ psql: warning: extra command-line argument "X,ip_tmp" ignored
を見るだけでも、Xと , と ip_tmp がひとまとまりだと解釈さえている様子がうかがえます。


区切りの閑馬を使っていますが、-v オプションの説明にはどこにも「複数指定する場合はカンマで区切る」とは書いてありません。
「セミコロンで...」等とも書かれていません。
複数書けるなら、どうやって複数書くのか、ちゃんと記述があるべきものです。
以上から、ということは、複数書けないのではないか、という予想もできます。

コマンドラインのツールを使っていると、同じオプションを複数指定する、というのはよくみかけます。

この回答へのお礼

回答ありがとうございます。
まだまだシェルスクリプト、sqlについての知識が足りていないので細かく説明していただけると本当にありがたいです。
-vを複数書くことで複数の変数の引き渡しが可能になりました。
warningの意味もあまりわからなくて今回のことで解決しました。
今後、\setだけのSQLファイルを用意して実行して定義されている変数確認してみます。

変数引き渡しは可能になったのですがtest.sqlで以下のエラーが起きました。

104.140.188.38のOSはLinux 2.2.x-3.x (barebone)です。
ERROR: column ":col" of relation "ostable" does not exist
LINE 1: update ostable set ":col"=1
^
QUERY: update ostable set ":col"=1
CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement


updateコマンドで列名を指定する際に変数を用いるとエラーが起きています。
update ostable set :col=1としたところ
ERROR: syntax error at or near ":"が起こりました。
update ostable set ':col'=1でもERROR: syntax error at or near ":"が起こりました。
ostableの列名に数字を用いており、テーブル作成の際に""をつけて作成しています。
create table ostable(ip text, os text, "1" integer, ・・)
なのでupdate ostable set ":col"としましたがエラーが起きています。
調べたところupdateコマンドでは変数用いることができないということも書いていました。何か解決方法ご存知でしょうか？

お礼日時：2020/01/08 14:23