JSON in Trigger Function



  • I use this extension https://github.com/supabase/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?



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2