1
CREATE OR REPLACE FUNCTION notify_tenant_work_order_status_cud() RETURNS TRIGGER AS $$
DECLARE
row RECORD;
output JSONB;
row_obj JSONB;
payload JSONB;
final_payload JSONB;

BEGIN
-- Checking the Operation Type
IF (TG_OP = 'DELETE') THEN
  row = OLD;
ELSE
  row = NEW;
END IF;

-- Forming the Output as notification. You can choose you own notification.
output = jsonb_build_object('operation', TG_OP);
row_obj = jsonb_build_object('row', to_jsonb(row));
payload = output || row_obj;
final_payload = jsonb_build_object('id', jsonb_extract_path_text(payload, 'row', 'id'), 'operation', jsonb_extract_path_text(payload, 'operation'));

-- Calling the pg_notify for my_table_update event with output as final_payload

PERFORM pg_notify('tenant_work_order_status_cud'::text, final_payload::text);

-- Returning null because it is an after trigger.
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_tenant_work_order_status_cud AFTER INSERT OR UPDATE OR DELETE ON tenant_work_order_status FOR EACH ROW EXECUTE PROCEDURE notify_tenant_work_order_status_cud();

when any Insert,Update and Delete operations are perform on the tenant_work_order_status table it should notify on the tenant_work_order_status_cud() channel. It is not working as expected.

We have same triggers on other table, where it works fine.

Any possible help would be appreciated.

3
  • 1
    Define 'It is not working as expected.'? The notification does not happen? It does but with incorrect information? Other reason(s)? Also the comment says '...pg_notify for my_table_update event...' yet the pg_notify has 'tenant_work_order_status_cud', is that correct? Add answers as update to your question. Commented Dec 28, 2021 at 16:39
  • Issue was with the payload limit of 8000 byte, when the payload is more than 8000 then it would not notify on the channel. Now just using few columns for payload to bring down the payload below 8000 bytes. Commented Dec 30, 2021 at 10:49
  • @kushpatel as Adrian Klaver said above, please add any further info as edits to your question and/or please mark the below answer as "correct" if it provided the solution. Commented Mar 8, 2023 at 16:49

1 Answer 1

1

Make sure the payload is not more than 8000 bytes, the pg-channel will only be able to carry less than 8000 bytes.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.