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

2020年7月23日2022年3月17日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に変数を渡す方法でした。

参考書

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