Reputation: 6246
I'm trying to build the object to JSON using the function json_build_object() which is not working in PostgreSQL 9.2.I'm having trouble figuring out the best way to find the alternative of json_build_object in 9.2.json_build_object() the function is added from the 9.4 version.
-- Function: queue_event()
-- DROP FUNCTION queue_event();
CREATE OR REPLACE FUNCTION queue_event()
RETURNS trigger AS
$BODY$
DECLARE
data json;
notification json;
BEGIN
-- Convert the old or new row to JSON, based on the kind of action.
-- Action = DELETE? -> OLD row
-- Action = INSERT or UPDATE? -> NEW row
IF (TG_OP = 'DELETE') THEN
data =row_to_json(OLD);--row_to_json
ELSE
data =row_to_json(NEW);--row_to_json
END IF;
-- Construct the notification as a JSON string.
notification = json_build_object(
'table',TG_TABLE_NAME,
'action', TG_OP,
'data', data); ---- Alternative to this function in 9.2 whereas in 9.4 version it is working fine.
-- Execute pg_notify(channel, notification)
PERFORM pg_notify('q_event',notification::text);
-- Result is ignored since this is an AFTER trigger
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION queue_event()
OWNER TO postgres;
Upvotes: 0
Views: 1548
Reputation: 222722
Does it work with row_to_json()
and select into
?
select row_to_json(x) into notification
from (values(TG_TABLE_NAME, TG_OP, data)) as x("table", action, data)
Upvotes: 2