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;
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?
