PLpgSQL クイズ

ワケあってPLpgSQL + Triggerのクイズを解いたのですが、PLpgSQLの基本はもとより、pg_notify()やらCTEやらJSONやら、実力を試すにはよいクイズだったように思うので、上げておきます。

質問

以下のテーブルがある。

CREATE TABLE transaction (
       id SERIAL PRIMARY KEY,
       payment_id BIGINT,
       trade_id BIGINT
);

CREATE TABLE payment (
       id SERIAL PRIMARY KEY,
       tid BIGINT REFERENCES transaction(id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
       details JSON NOT NULL
);

CREATE TABLE trade (
       id SERIAL PRIMARY KEY,
       tid BIGINT REFERENCES transaction(id) ON UPDATE RESTRICT ON DELETE RESTRICT NOT NULL,
       details JSON NOT NULL
);

ALTER TABLE transaction ADD FOREIGN KEY (payment_id)
REFERENCES payment(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE transaction ADD FOREIGN KEY (trade_id)
REFERENCES trade(id) ON UPDATE RESTRICT ON DELETE RESTRICT;

次のような操作を行う (中身は本質的ではない)。どちらも自分自身とtransactionテーブルにデータを放り込む。

WITH op(details, tid, pid) AS 
(VALUES ('{"data":"details"}'::JSON,
     nextval('transaction_id_seq'), 
     nextval('payment_id_seq'))
),
tr AS (
   INSERT INTO transaction(id, payment_id)
   SELECT op.tid, op.pid FROM op RETURNING *
),
pm AS (
   INSERT INTO payment(id, tid, details)
   SELECT op.pid, op.tid, op.details FROM op RETURNING *
)
SELECT tr AS transaction, pm AS payment FROM tr CROSS JOIN pm;

WITH op(details, tid, trid) AS (
VALUES ('{"data":"details"}'::JSON,
    nextval('transaction_id_seq'),
    nextval('trade_id_seq'))
),
tr AS (
   INSERT INTO transaction(id, trade_id)
   SELECT op.tid, op.trid FROM op RETURNING *
),
td AS (
   INSERT INTO trade(id, tid, details) 
   SELECT op.trid, op.tid, op.details FROM op RETURNING *
)
SELECT tr AS transaction, td AS trade FROM td CROSS JOIN tr;

問題は"テーブルtransactionのトリガを作れ。ただし、tradeとpaymant両テーブルの変更分をpayloadとしてJSON形式でNOTIFYするように。"

答え

私の答え。

CREATE OR REPLACE FUNCTION trans_update () RETURNS trigger AS
$$
DECLARE
  target text;
  target_id bigint;
  payload text;
  query text;
BEGIN
 target := 'payment';
 SELECT NEW.payment_id INTO target_id;
 IF target_id IS NULL THEN
    target := 'trade';
    SELECT NEW.trade_id INTO target_id;
 END IF;
 query := 'SELECT to_json(t)  FROM ' || target || ' as t WHERE  t.id = ' || target_id;
 EXECUTE query INTO payload; 
 PERFORM pg_notify(target,  payload);
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trans_trig AFTER INSERT ON transaction
       FOR EACH ROW EXECUTE PROCEDURE trans_update();

実行結果は以下。

sampledb=# LISTEN trade; LISTEN payment;
LISTEN
LISTEN
sampledb=# WITH op(details, tid, trid) AS (VALUES ('{"data":"details"}'::JSON,
     nextval('transaction_id_seq'), nextval('trade_id_seq'))),
tr AS (INSERT INTO transaction(id, trade_id) SELECT op.tid, op.trid FROM op RETURNING *),
td AS (INSERT INTO trade(id, tid, details) SELECT op.trid, op.tid, op.details FROM op RETURNING *)
SELECT tr AS transaction, td AS trade FROM td CROSS JOIN tr;
 transaction |              trade               
-------------+----------------------------------
 (19,,10)    | (10,19,"{""data"":""details""}")
(1 row)

Asynchronous notification "trade" with payload "{"id":10,"tid":19,"details":{"data":"details"}}" received from server process with PID 25552.
sampledb=# WITH op(details, tid, pid) AS (VALUES ('{"data":"details"}'::JSON,
     nextval('transaction_id_seq'), nextval('payment_id_seq'))),
tr AS (INSERT INTO transaction(id, payment_id) SELECT op.tid, op.pid FROM op RETURNING *),
pm AS (INSERT INTO payment(id, tid, details) SELECT op.pid, op.tid, op.details FROM op RETURNING *)
SELECT tr AS transaction, pm AS payment FROM tr CROSS JOIN pm;
 transaction |             payment              
-------------+----------------------------------
 (20,10,)    | (10,20,"{""data"":""details""}")
(1 row)

Asynchronous notification "payment" with payload "{"id":10,"tid":20,"details":{"data":"details"}}" received from server process with PID 25552.

こんなのいきなり30分から1時間くらいで解けと言われても、しばらくPLpgSQLもトリガも書いてないしNOTIFYも完全に忘れきっていたので1時間では無理だった。リハビリが必要。