PG_CACHE_TRIGGER variable

Default PL/pgSQL trigger function installed on every watched table.

It builds a JSON payload of the changed row and issues PG_NOTIFY on a channel named after the table. The payload shape is ChannelPayload: timestamp, operation, schema, table and the row itself — NEW for inserts and updates, OLD for deletes.

Column values are read out of information_schema and cast to TEXT, so every field arrives as a string regardless of its SQL type.

Note PostgreSQL caps a NOTIFY payload at 8000 bytes; a change to a very wide row can exceed that and the notification will be rejected. Override with PgCacheOptions.triggerDefinition if the default does not suit — see PgCacheOptions.

Signature:

PG_CACHE_TRIGGER = "CREATE FUNCTION post_change_notify_trigger()\nRETURNS TRIGGER\nLANGUAGE plpgsql\nAS $$\nDECLARE\n    rec RECORD;\n    payload TEXT;\n    payload_items TEXT[];\n    column_names TEXT[];\n    column_name TEXT;\n    column_value TEXT;\n    channel CHARACTER VARYING(255);\nBEGIN\n    channel := TG_TABLE_NAME;\n\n    CASE TG_OP\n        WHEN 'INSERT', 'UPDATE' THEN rec := NEW;\n        WHEN 'DELETE' THEN rec := OLD;\n        ELSE RAISE EXCEPTION 'NOTIFY: Invalid operation \"%\"!',\n            TG_OP;\n    END CASE;\n\n    SELECT array_agg(\"c\".\"column_name\"::TEXT)\n    INTO column_names\n    FROM \"information_schema\".\"columns\" AS \"c\"\n    WHERE \"c\".\"table_name\" = TG_TABLE_NAME;\n\n    FOREACH column_name IN ARRAY column_names\n    LOOP\n        EXECUTE FORMAT('SELECT $1.%I::TEXT', column_name)\n            INTO column_value\n            USING rec;\n\n        payload_items := ARRAY_CAT(\n            payload_items,\n            ARRAY [column_name, column_value]\n        );\n    END LOOP;\n\n    payload := json_build_object(\n        'timestamp', CURRENT_TIMESTAMP,\n        'operation', TG_OP,\n        'schema', TG_TABLE_SCHEMA,\n        'table', TG_TABLE_NAME,\n        'record', TO_JSON(JSON_OBJECT(payload_items))\n    );\n\n    PERFORM PG_NOTIFY(channel, payload);\n\n    RETURN rec;\nEND;\n$$;\n"

Read this page as plain markdown — no HTML, no navigation. For pasting into an LLM, or for an agent to fetch.