PostgreSQL – Update Dynamic Column Names Using Keys from JSONB Argument

dynamic-sqljsonpostgresqlstored-proceduresupdate

I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, but I'm trying to push as much code down into the db layer as possible.

This is the kind of thing I naively hoped might work:

CREATE PROCEDURE record_event(
  foo_arg integer,
  name_arg text,
  data_arg jsonb,
  occurred_at_arg timestamptz
)
AS $$
DECLARE column_name text;
BEGIN
  -- This part is incidental:
  INSERT INTO foo_event(foo, name, data, occurred_at)
    VALUES(foo_arg, name_arg, data_arg, occurred_at_arg);

  -- This is the part I'm struggling with:
  FOR column_name IN (SELECT * FROM jsonb_object_keys(data_arg)) LOOP
    PREPARE update_query(text, text, integer) AS
      UPDATE foo SET $1 = $2 WHERE id = $3;
    EXECUTE update_query(column_name, data_arg->>column_name, foo_arg);
  END LOOP;
END
$$ LANGUAGE plpgsql;

But pg doesn't like $1 in that position:

psql:src/db/migrations/foo/up.sql:43: ERROR:  syntax error at or near "$1"
LINE 15:       UPDATE foo SET $1 = $2 WHERE id = $3;

I also noticed there is a json_populate_record() function which seems like it should be helpful here, but I don't really understand how to apply it to my situation from the documentation.

Does what I'm trying to do make sense and is something like it possible?

Best Answer

Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling.

PROCEDURE calling UPDATE for each JSON key

Proof of concept to show what did not work in your attempt.

CREATE PROCEDURE record_event (
   foo_arg int,
   name_arg text,
   data_arg jsonb,
   occurred_at_arg timestamptz
) AS
$proc$
DECLARE
   column_name text;
BEGIN
   INSERT INTO foo_event
        (foo    , name    , data    , occurred_at)
   VALUES(foo_arg, name_arg, data_arg, occurred_at_arg);

   -- works, but inefficiently:
   FOR column_name IN 
      SELECT * FROM jsonb_object_keys(data_arg)
   LOOP
      EXECUTE format('UPDATE foo SET %I = $1 WHERE id = $2', column_name)
      USING data_arg->>column_name, foo_arg;
   END LOOP;
END
$proc$  LANGUAGE plpgsql;

Call:

CALL record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

Column names cannot be dynamic, so format the query (with format() for convenience) and use EXECUTE. But values are better provided with the USING clause.

Note the format specifier %I, but the parameters $1 and $2 refer to values provided by the USING clause (not to function parameters!).

But I would not use it. Multiple UPDATE commands are pretty inefficient. Instead use a ...

Function with a single UPDATE

Should be much more efficient.

CREATE OR REPLACE FUNCTION record_event(
   foo_arg int,
   name_arg text,
   data_arg jsonb,
   occurred_at_arg timestamptz
)
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN
   INSERT INTO foo_event
          (foo    , name    , data    , occurred_at)
   VALUES (foo_arg, name_arg, data_arg, occurred_at_arg);

   SELECT INTO _sql
          'UPDATE foo SET '
       || string_agg(format('%I = %L', key, value), ', ')
       || ' WHERE id = ' || foo_arg
   FROM   jsonb_each_text(data_arg);

   IF _sql IS NOT NULL THEN
      -- RAISE NOTICE '%', _sql; -- uncomment instead of EXECUTE to debug
      EXECUTE _sql;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

We could pass values with another USING clause. But simply concatenate the whole command for convenience.

Using a FUNCTION as there is nothing that would require a PROCEDURE (new since Postgres 11). Would work as procedure too, though.

As always, properly quote identifiers and values to defend against possible SQL injection. I only dare to concatenate foo_arg directly, as an integer value is safe in this regard and works as unquoted numeric literal.

Note that column names are treated as case-sensitive.

Related:

Related Question