PostgreSQL SQLで外部プログラムを実行する方法

Tweet

エコモットアドベントカレンダー14日目のエントリーです。

COPYコマンドのPROGRAMパラメータを利用するとコマンド実行が出来ます。
本来は返り値をテーブルにコピーするために利用するものなのでしょうが、
今回は任意のコマンドを叩くためだけに利用します。

まず空テーブルを用意します。
database# CREATE TABLE empty_table (col1 text);
CREATE TABLE

下記SQLを実行します。
database# COPY empty_table FROM PROGRAM 'ls /usr/local/pgsql > ~/output/output.txt';
COPY 0

$ /home/postgres/output/ls
output.txt

output.txtが作成されていることが確認できます。
postgresの実行ユーザにてコマンドが実行されるので注意して下さい。

$ vim output.txt
bin
data
include
lib
share

lsの内容が記述され、lsが実行されたことが伺えます。

空テーブルを用意せずに既存テーブルでもコマンドは実行出来ますが、

database# COPY empty_table FROM PROGRAM 'ls /usr/local/pgsql';

間違ってこうしてしまうと ls コマンドの実行結果をCOPYしてしまうので、
空テーブルを作っておいたほうが無難です。
実行結果を残す必要が無い場合は /dev/null してしまいましょう。

SQLなんか使わないでコマンド直接叩けば？とお思いでしょう。
その通りなんですが、トリガーと組み合わせると便利になります。
その辺りは次回。