JSON in Trigger Function

The question:

I use this extension pg_net to call external apis.
The trigger that I have created is below:

begin 
perform   
net.http_post(         
url:='https://httpbin.org/post',   
body:='{"msg": "'+NEW.message+'" ,"hello": "world"}'::jsonb     
) as request_id;  
RETURN NULL; 
end;

If I run the trigger I get this error

“No operator matches the given name and argument types. You might need to add explicit type casts. operator does not exist: unknown + text”

If I run it without the NEW.message (which I want) I don’t get any error.

Any idea?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Use jsonb_build_object() it will automatically take care of any embedded quotes in the value so you don’t have to escape them manually.

begin 
perform   
net.http_post(         
  url := 'https://httpbin.org/post',   
  body := jsonb_build_object('msg', NEW.message, 'hello', 'world')
  ) as request_id;  
RETURN NULL; 
end;

Note that using := for named parameters is deprecated in favor of the standard compliant =>


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment