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?
1 Answer 1
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)
LANGUAGE plpgsql 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$;
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
LANGUAGE plpgsql 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$;
Call:
SELECT record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());
We could pass values to EXECUTE 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 case-sensitively.
Related:
Explore related questions
See similar questions with these tags.