PostgreSQLのトリガ関数からbashスクリプトを実行する

PostgreSQLのトリガ関数からOS側に配置したbashスクリプトを起動する手順をまとめました。私のようにPostgreSQLを前提とするOSS製品を利用しており、プログラムのソースコード修正は避けたいものの、特定テーブル更新を契機に簡易処理を動かしたい場合に適した手法だと思います。

なお、トリガから呼べる関数はplpgsqlやC言語であり、直接任意のOSコマンドを実行することはできないようでしたので、以下を参考にplpgsqlの関数定義のなかで、copyコマンドを利用することでOSコマンドを実行可能することにしました。

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

以下、実装例です。

動作環境

- OS:WSL2上のRockyLinux9
- podmanコンテナ上のpostgres:15
※本環境をセットアップする手順は以下の記事参考
https://note.com/keyton61/n/n8c919178a8c1

実装例説明

テストテーブルtestのageカラムが更新され場合に、トリガ関数が呼ばれてtest.shスクリプトを実行する。
トリガ関数内で更新後のageの値をtest.shスクリプトの引数に指定する。
test.shスクリプトは引数で受け取った値をtest.txtファイルに追記していく。

動作確認用のテストテーブル作成

create table test (  id integer,  name text,  age integer);
insert into test values (1, 'aaa', 1);
insert into test values (2, 'bbb', 14);
insert into test values (3, 'ccc', 14);

COPYコマンド実行時にCOPY先に指定するダミーテーブルの作成

create table empty_table (  dummy_id integer);

トリガーから起動する関数の作成

関数名はrun_bash_scriptとし、COPYコマンドを用いてファイルシステム側に配置した`/var/lib/postgresql/test.sh`を実行する。
関数の戻り値はtrigger型を指定する。
COPYコマンドのFROM句でPROGRAMを指定することでOSコマンドを実行する。bashスクリプトに引数（NEW.age）を渡すため、EXECUTE文の中で文字列化したCOPY文を実行している。
SQL部分の戻り値はnullを指定する。

CREATE OR REPLACE FUNCTION run_bash_script() RETURNS trigger AS
$$
DECLARE
    cmd text := '/bin/bash /var/lib/postgresql/test.sh ' || NEW.age;
BEGIN
    EXECUTE 'COPY empty_table FROM PROGRAM ''' || cmd || '''';
    RETURN null;
END;
$$ LANGUAGE plpgsql;

トリガーの作成

トリガ名はupdate_ageとする。
トリガの起動タイミングにはAFTER（testテーブルの更新後）を指定する。
また、FOR EACH ROWを指定してテーブルの行が更新されるたびにトリガを起動する。
また、WHEN句を利用してトリガ起動するのはageカラムが更新された場合に限定する。
最後に、UPDATE後のageカラムの値をrun_bash_script関数に引き渡す。

CREATE OR REPLACE TRIGGER update_age
AFTER UPDATE ON public.test
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age)
EXECUTE PROCEDURE
public.run_bash_script();

bashスクリプトの配置

cat << "EOF" > /var/lib/postgresql/test.sh
#!/bin/bash
echo $1 >> /var/lib/postgresql/test.txt
EOF

動作確認

postgres=# select * from test;
 id | name | age 
----+------+-----
  1 | aaa  |   1
  2 | bbb  |  14
  3 | ccc  |  14
(3 rows)

postgres=# update test set age=23 where id=3;
UPDATE 1
postgres=# 
postgres=# 
postgres=# select * from test;
 id | name | age 
----+------+-----
  1 | aaa  |   1
  2 | bbb  |  14
  3 | ccc  |  23
(3 rows)

postgres=# 
postgres=# 
postgres=# \! ls /var/lib/postgresql/
bbb.log  data  test.sh  test.txt
postgres=# \! cat test.txt
23 ## 更新後のageが追記された
postgres=# 
postgres=# update test set age=20 where id=3;
UPDATE 1
postgres=# 
postgres=# 
postgres=# \! cat test.txt
23
20 ## 更新後のageが追記された
postgres=#