How to convert NEW/OLD object to text in Postgresql?-CodePudding

I have been trying to convert OLD/NEW object to json formatted text in my function.

I tried solutions in this topic but it can only get a single field in json. I want to convert all object to text.

What I tried so far is something like that;

 CONCAT('{ "ChangeType" : "', 'INSERT',
            '", "NEW" : "',  row_to_json(NEW) ->> '{}', -- returned empty
            '", "NEW" : "',  row_to_json(NEW) ->> 0, -- returned empty
            '", "NEW" : "',  json_extract_path_text(row_to_json(NEW), '{}'), -- returned empty
             '", "NEW" : "',  row_to_json(NEW) #>> '{}', -- cannot execute json syntax error
            '" }')::json

I am looking this documentation but honestly could not achieve it. Basically, instead of getting single field from '{"a": {"b":"foo"}}' , I would like to get this object as a single text.

Do you have any idea how can I convert NEW/OLD to all text or convert JSON Object to text ?

CodePudding user response：

if you are use this block:

CONCAT('{ "ChangeType" : "', 'INSERT',
            '", "NEW" : "',  row_to_json(NEW)::text,
            '" }')::json

you have incorrect syntax on this code, because you have additional quotes in this code for values NEW, so in JSON format, if the value of any key is JSON, it should be written without quota. Correct syntax is it:

CONCAT('{ "ChangeType" : "', 'INSERT',
            '", "NEW" : ',  row_to_json(NEW)::text,
            ' }')::json