I am using the code below to create a function with variables that updates a jsonb column with a json object or create it if it doesn’t exist – With help from this post

However I am really having trouble interpolating the variables $2 and $3 in the json string. Any suggestions?

CREATE OR REPLACE FUNCTION public.updateoffset(site text, offsetnumber integer, toscrape integer)
 RETURNS void
 LANGUAGE sql
AS $function$


 update settings set "offset" = coalesce("offset", '{}') || '{"$2": {"toscrape":3$}}'
  where site = $1;
$function$ 

>Solution :

Use the function format().

...
  update settings
  set "offset" = 
    coalesce("offset", '{}') || format('{"%s": {"toscrape":%s}}', $2, $3)::jsonb
  where site = $1;
...