sql - Modyfing result in postgresql funtion that watches modification on table

Question

I need some help

I needed something to watch changes on specific postresql table and send it back as event, so I used something like this:

DROP TRIGGER IF EXISTS "OnDataChange" on public."SystemStates";
DROP FUNCTION IF EXISTS "NotifyOnDataChange";

CREATE FUNCTION public."NotifyOnDataChange"()
  RETURNS trigger
  LANGUAGE 'plpgsql'
AS $BODY$ 
DECLARE 
  data JSON;
  notification JSON;
BEGIN
  IF (TG_OP = 'DELETE') THEN
    data = row_to_json(OLD);
  ELSE
    data = row_to_json(NEW);
  END IF;
  notification = json_build_object('table',TG_TABLE_NAME, 'action', TG_OP, 'data', data);  
    PERFORM pg_notify('datachange', notification::TEXT);
  RETURN NEW;
END
$BODY$;

CREATE TRIGGER "OnDataChange"
  AFTER INSERT OR DELETE OR UPDATE 
  ON public."SystemStates"
  FOR EACH ROW
  EXECUTE PROCEDURE public."NotifyOnDataChange"();

And it works whenever I made changes on table public."SystemStates" i get notified i my application.

If anyone is interested here is event reader in c#:

    static async Task Main(string[] args)
    {
        await using var conn = new NpgsqlConnection("<conn string>");
        await conn.OpenAsync();
        conn.Notification += (o, e) => Console.WriteLine("Received notification: " + e.Payload);

        await using (var cmd = new NpgsqlCommand("LISTEN datachange;", conn))
            cmd.ExecuteNonQuery();

        while (true)
            conn.Wait(); 
    }

and result would look like:

 {"table" : "SystemStates", "action" : "UPDATE", "data" : {"Id":23,"ExtComm":0,"IntComm":0,"State":0,"SystemId":1,"LastCommunicationTimestamp":"2021-01-20T11:56:34.704435"}}

But I was wondering, because I don't need whole row information only couple of columns, on other hand I would like to get also one information from related table. So my table would look like:

 Id | ExtComm | IntComm | State | SystemId | LastCommTimestamp 
----+---------+---------+-------+----------+------------------+

All I need is LastCommTimestamp and one column from table System with this table is related by SystemId (column name is System Name).

As far I can deduct result and serialization are here :

  IF (TG_OP = 'DELETE') THEN
    data = row_to_json(OLD);
  ELSE
    data = row_to_json(NEW);
  END IF;

But I don't know how to bite this thing

EDIT:

I tried doing some sql in

data = row_to_json(NEW);

Something like:

SELECT row_to_json(ROW)
INTO data
FROM (
    SELECT PUBLIC."Systems"."MPV01CabinetId" AS "MpvCid", PUBLIC."SystemStates"."LastCommTimestamp" AS "Timestamp"
        FROM PUBLIC."SystemStates" 
        LEFT JOIN PUBLIC."Systems" ON PUBLIC."SystemStates"."SystemId" = PUBLIC."Systems"."Id"
) ROW;

Well it worked but I've got last record of the table (witch in my opinion is logical as I am returning record type as json).

So now I am trying to use NOW as object so it means replacing PUBLIC."SystemStates" with NOW."LastCommTimestamp" but postresql says that this property does not exists.

Am I using It wrong?

question from:https://stackoverflow.com/questions/65882139/modyfing-result-in-postgresql-funtion-that-watches-modification-on-table

Answer 1

Ok I found what was wrong, I misinterpreted function row_to_json and when I wanted to get simple value form NOW (for example NOW.id) it expected record not typed value.

Also my SQL query was wrong and I tried using FROM NOW, now I am using this as a parameter and it is working as expected (But if anyone sees room for improvement in this sql query I will be most fully grateful)

DROP TRIGGER IF EXISTS "OnDataChange" on public."SystemStates";
DROP FUNCTION IF EXISTS "NotifyOnDataChange";

CREATE FUNCTION public."NotifyOnDataChange"()
  RETURNS trigger
  LANGUAGE 'plpgsql'
AS $BODY$ 
DECLARE 
  recordvalues json;
  data JSON;
  notification JSON;
BEGIN
  IF (TG_OP = 'DELETE') THEN
    data = row_to_json(OLD);
  ELSE
     SELECT row_to_json(ROW)
        INTO data
        FROM (
            SELECT PUBLIC."Systems"."MPV01CabinetId" AS "MpvCid", PUBLIC."SystemStates"."LastCommunicationTimestamp" AS "Timestamp"
            FROM PUBLIC."Systems"
            right JOIN PUBLIC."SystemStates" ON PUBLIC."SystemStates"."SystemId" = PUBLIC."Systems"."Id"
            WHERE PUBLIC."Systems"."Id" = NEW."SystemId"
        ) ROW;
  END IF;
  notification = json_build_object('table',TG_TABLE_NAME, 'action', TG_OP, 'data', data);  
    PERFORM pg_notify('datachange', notification::TEXT);
  RETURN NEW;
END
$BODY$;

CREATE TRIGGER "OnDataChange"
  AFTER INSERT OR DELETE OR UPDATE 
  ON public."SystemStates"
  FOR EACH ROW
  EXECUTE PROCEDURE public."NotifyOnDataChange"();