JSON in Trigger Function

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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 =>

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Tags:

Leave a Reply