シェルスクリプト+psqlでカレントスキーマを変更しつつSQLを実行する

背景

シェルスクリプトで、スキーマを切り替えつつ、該当の全てのスキーマにSQLを実行する必要があったのでメモを残します。

調査

select current_schema();

でカレントスキーマの情報が取得でき、(大抵の場合はpublic)

set search_path to $schema;

で目的のスキーマに変更ができました。

なのでこのコマンドを-cで実行しつつ、-fでSQLファイルを指定する組み合わせを行うとOKです。

psql -d ${DBNAME} -U ${USER} -c "set search_path to $schema;" -f ${sqlfile}

でカレントスキーマを切り替えて${sqlfile}を実行できます。

script sample

ざっと書くと

schemas=`psql -d ${DBNAME} -t -c "select distinct table_schema from information_schema.tables where table_schema like '${schemaname}%' and table_schema not in('public','pg_catalog','information_schema')"`

for schema in $schemas
do
  for sql in `ls`
  do
    if [[ $sql == *".sql" ]]
    then
      #ファイル名は${tablename}.sqlの想定です
      table=`echo $sql | sed -e "s/.sql//"`
      echo "process > ${schema}.${table}"
      psql -U ${USER}-d ${DBNAME} -c "set search_path to $schema" -f $sql
    fi
  done
done

もちろんSQLファイルに"public."とかスキーマが書かれていると困るので

sed -i -e "s/public.//g" $sql

とかでスキーマ定義を無くす必要はあります。

感想

スキーマ切り替え方法は上記のようにカレントスキーマを変更する方法の他に:SCHEMAなどの特定の文字列を対象のスキーマ名に置き換えつつ実行する手法もありますがどちらが一般的なんでしょうか。
個人的にはpsqlで-cと-fが同時に使えるのは意外でした。