CODEWITHSUNDEEP
postgresqltriggerssql-updatesql-insertpostgresql-9.2
Ajay Takur
Ajay Takur

Reputation: 6246

Alternative function of json_build_object() in PostgreSQL 9.2

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

Answers (1)

GMB
GMB

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

Related Questions