【PostgreSQL】バッチからSQLに変数を渡す【BAT】

BAT,CODE,PostgreSQL,データベース

バッチファイルで設定した変数をSQLに渡す方法です。

設定方法 ①バッチファイルの変数をSQLに設定する方法
②変数を外部ファイルから読み込む方法

各種設定

データベース設定

データベース名:postgres
テーブル名:test_table
ユーザー名:postgres

テーブル設定

こちらで作成したものを使用します:【SQL】サンプルテーブルを作成するSQL

通常のSQL実行結果

select * from test_table where price = 270;

【実行結果】price = '270'の3レコードが選択されます。

      menu       | category | price | cal
-----------------+----------+-------+-----
 Blended coffee  | drink    |   270 |   7
 American coffee | drink    |   270 |   7
 Espresso coffee | drink    |   270 |  10
(3 行)

①バッチファイルで設定した変数をSQLファイルに渡す方法

実行元となるバッチファイル

  • -v オプションを指定します
  • SQLに渡す変数名(PRICE)と値(270)を設定します
@echo off
rem --------------------------------------------------
rem DB接続パラメータ
rem --------------------------------------------------
set PGPATH=C:\"Program Files"\PostgreSQL\10\bin\
set HOSTNAME=localhost
set PORTNUM=5432
set DBNAME=postgres
set USERNAME=postgres
set PGPASSWORD=postgres

rem --------------------------------------------------
rem bat実行パラメータ
rem --------------------------------------------------
set SQLFILE=input.sql

rem --------------------------------------------------
rem SQL実行
rem --------------------------------------------------
%PGPATH%psql -h %HOSTNAME% -p %PORTNUM% -d %DBNAME% -U %USERNAME% -f %SQLFILE% -v PRICE=270

rem 処理を確認するため一時停止する
pause

実行されるinput.sqlファイル

  • :'変数名' で値を受け取る変数を記述します
  • ここでは'PRICE' が該当します
select * from test_table where price = :'PRICE';

【実行結果】変数の270がSQLに渡され、該当の3レコードが選択されました。

         menu         | category | price | cal
----------------------+----------+-------+-----
 blend coffee         | drink    |   270 |   7
 american coffee      | drink    |   270 |   7
 espresso cofee       | drink    |   270 |  10
(3 行)

②テキストファイルから変数を読み込んでSQLファイルに渡す方法

通常のSQL実行結果

select * from test_table where price = 270 and cal = 7;
select * from test_table where price = 270 and cal = 10;

【実行結果1】price = 270 かつ cal = 7 の該当は2レコードです。

      menu       | category | price | cal 
-----------------+----------+-------+-----
 Blended coffee  | drink    |   270 |   7
 American coffee | drink    |   270 |   7
(2 行)

【実行結果2】price = 270 かつ cal = 10 の該当は1レコードです。

      menu       | category | price | cal 
-----------------+----------+-------+-----
 Espresso coffee | drink    |   270 |  10
(1 行)

実行元となるバッチファイル

  • input.txt から変数を読み込んでSQLを繰り返し実行します
  • 1,2,3列目の値をバッチの変数 i, j, k に格納します
  • input.txtのヘッダの読み込みはスキップします
@echo off
rem --------------------------------------------------
rem DB接続パラメータ
rem --------------------------------------------------
set PGPATH=C:\"Program Files"\PostgreSQL\10\bin\
set HOSTNAME=localhost
set PORTNUM=5432
set DBNAME=postgres
set USERNAME=postgres
set PGPASSWORD=postgres

rem --------------------------------------------------
rem bat実行パラメータ
rem --------------------------------------------------
set TXTFILE=input.txt
set SQLFILE=input.sql

rem --------------------------------------------------
rem SQL実行
rem --------------------------------------------------
for /f "tokens=1,2,3 skip=1 delims=," %%i in (%TXTFILE%) do (
    %PGPATH%psql -h %HOSTNAME% -p %PORTNUM% -d %DBNAME% -U %USERNAME% -f %SQLFILE% -v PRICE=%%j -v CAL=%%k -o result_%%i.log
)

変数を記述するinput.txtファイル

  • カンマ区切りで値を記述します
  • ヘッダはバッチ側で読み込まない設定にしています
  • no列がバッチの変数i、price列が変数j、cal列が変数kに順次格納されます
no,price,cal
1,270,7
2,270,10

変数を受け取るinput.sql

  • :'PRICE':'CAL' で変数値を受け取っています
  • input.txt → バッチの変数 → SQL へと変数が渡されていきます
select * from test_table where price = :'PRICE' and cal = :'CAL';

【実行結果1】result_1.logに結果が出力されました。

      menu       | category | price | cal 
-----------------+----------+-------+-----
 Blended coffee  | drink    |   270 |   7
 American coffee | drink    |   270 |   7
(2 行)

【実行結果2】result_2.logに結果が出力されました。

      menu       | category | price | cal 
-----------------+----------+-------+-----
 Espresso coffee | drink    |   270 |  10
(1 行)

以上、バッチからSQLに変数を渡す方法でした。

参考書

ググっても見つからないものが、書籍ではさらっと書かれていたりします。