Unable to add new pairs to JSON object in a Supabase Function

Question

I try to create a function that adds a new pair to the object, but I get an error. I don't understand what could be the reason, explain to me stupid what's wrong here. thank you)

create or replace function add_to_json(_id int, _field text, _obj text)
returns text as
$$
begin
  execute format('UPDATE public.posts SET %s = %s::jsonb || jsonb %s WHERE id = %s', _field, _field, _obj, _id);
  return  format('UPDATE public.posts SET %s = %s::jsonb || jsonb %s WHERE id = %s', _field, _field, _obj, _id);
end
$$
language plpgsql volatile;

select add_to_json(1, 'likes', '{"fff":123}')

ERROR: Failed to run sql query: syntax error at or near "{"

Erwin Brandstetter · Accepted Answer · 2022-12-15T14:06:56.873

This should work:

CREATE OR REPLACE FUNCTION add_to_json(_id int, _field text, _obj jsonb)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text := format('UPDATE public.posts SET %1$I = %1$I || $1 WHERE id = $2', _field);
BEGIN
   EXECUTE _sql USING _obj, _id;
   RETURN _sql;
END
$func$;

Besides syntax errors and inefficiency, your original was wide open to SQL injection. Note how I pas values as values with the USING clause to EXECUTE. Only concatenating the column name requires dynamic SQL.

I also suggest to pass _obj jsonb to begin with and not need a later cast - if possible.

See:

OMG, thank you so much. Only you helped me today. – ghtx280 Dec 15 '22 at 14:06 — ghtx280, Dec 15 '22 at 14:06

Unable to add new pairs to JSON object in a Supabase Function

1 Answers1