使用 jsonb 參數中的鍵更新動態列名

我想為 Postgres 12 編寫一個通用過程，根據jsonb參數中提供的數據更新表的列。當然，它可以在應用程序邏輯中完成，但我試圖將盡可能多的程式碼下推到數據庫層。

這是我天真地希望可能起作用的那種事情：

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;

但是 pg 不喜歡$1那個位置：

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;

我還注意到有一個json_populate_record()似乎在這裡應該有用的功能，但我真的不明白如何從文件中將它應用於我的情況。

我正在嘗試做的事情是否有意義並且可能？

您的案例過於動態json_populate_record()：它採用了您在致電時不知道（也沒有）的記錄類型。

PROCEDURE呼叫UPDATE每個 JSON 鍵

概念證明，以顯示您的嘗試中沒有奏效的內容。

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 record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

列名不能是動態的，所以格式化查詢（format()為方便起見）並使用EXECUTE. 但是該子句更好地提供了值。USING

注意格式說明符%I，但參數$1和$2引用USING子句提供的值（不是函式參數！）。

但我不會使用它。多個UPDATE命令效率很低。而是使用 …

功能單一UPDATE

應該效率更高。

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;

稱呼：

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

我們可以USING用另一個子句傳遞值。但為了方便起見，只需連接整個命令。

使用 aFUNCTION因為沒有什麼需要 a PROCEDURE（自 Postgres 11 以來的新功能）。不過，也可以作為程序工作。

與往常一樣，正確引用標識符和值以防禦可能的 SQL 注入。我只敢foo_arg直接連接，因為一個integer值在這方面是安全的，並且作為未引用的數字文字工作。

請注意，列名被視為區分大小寫。

• PostgreSQL 列名是否區分大小寫？

有關的：

• Postgres 函式中的 SQL 注入與準備好的查詢
• 何時使用儲存過程/使用者定義函式？
• 如何根據json數據動態創建表？

引用自：https://dba.stackexchange.com/questions/252531