PostgresSQL_쉘을 이용하여 sql 문 반복수행하기

쉘을 이용하여 sql 문 반복수행하기

ㅁ postgres dba

출처: https://www.postgresdba.com/bbs/board.php?bo_table=B12&wr_id=50



loop.sql 화일은 현재시각을 표시하는 sql 문이 담겨져 있습니다.
이 구문을, 쉘스크립트를 이용하여 반복수행하는 스크립트를 만들도록 해보겠습니다.

이 쉘스크립트의 핵심은 세션을 한번만 생성한후 sql 문을 반복 수행한다는 점입니다.
즉, sql 반복수행을 위해 매번 세션을 새로 생성하지 않습니다. (logon/off 반복안함)

[enterprisedb@pgdbserver:/opt/PostgresPlus/9.3AS/oradba]$ cat loop.sql 
select current_timestamp;
[enterprisedb@pgdbserver:/opt/PostgresPlus/9.3AS/oradba]$ cat loop.sh 
#!/bin/sh
SLEEP_SEC=1
LOOP_MAX=3
COUNT=0

echo "\pset pager off"
while [ $COUNT -lt $LOOP_MAX ]
do
        echo "\i $1"
        sleep $SLEEP_SEC
        COUNT=`expr $COUNT + 1`
done
[enterprisedb@pgdbserver:/opt/PostgresPlus/9.3AS/oradba]$ ./loop.sh loop.sql | psql -U scott -d scottdb -X
Pager usage is off.
        current_timestamp         
----------------------------------
 01-JAN-15 21:54:48.373776 +09:00
(1 row)

        current_timestamp         
----------------------------------
 01-JAN-15 21:54:49.371446 +09:00
(1 row)

        current_timestamp         
----------------------------------
 01-JAN-15 21:54:50.378325 +09:00
(1 row)

[enterprisedb@pgdbserver:/opt/PostgresPlus/9.3AS/oradba]$