PLpgSQL QUIZ |

I found an interesting quiz about PLpgSQL.

Question (modified)

There are three tables:

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;

Basic operations are the following (those are not essential):

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;

Question is: “Make a trigger on the transaction table which notifies all information from both tables in JSON format.”

Ans.

My answer is the following:

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();

Results are below:

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.

PLpgSQL QUIZ

Question (modified)

Ans.

CATALOG

FEATURED TAGS