这个问题涉及使用 jsonb 参数中的键更新动态列名称,我的代码基于https://dba.stackexchange.com/users/3684/erwin-brandstetter提供的答案
我正在尝试编写一个通用函数,将数据从 jsonb 记录插入到具有多种数据类型的表中。如何将每个值动态转换为相应列的数据类型?
我正在使用 PostgreSQL 版本 15.2
对该函数的典型调用是:
json_data = '{
"text_field": "Some text",
"numeric_field": 17,
"datetime_field": "2023-04-24 11:06:50.36758-04",
"boolean_field": true
}'
SELECT * FROM insert_jsonb('schema_name', 'table_name', json_data);
我的功能:
CREATE OR REPLACE FUNCTION insert_from_jsonb(
sch_name text,
tbl_name text,
js jsonb,
out new_id uuid;
) AS
$func$
DECLARE
_sql text;
BEGIN
SELECT INTO _sql
'INSERT INTO ' || sch_name || '.' || tbl_name || ' ('
|| string_agg(format('%I', key), ', ') || ') VALUES ('
|| string_agg(format('%L', value), ', ') || ')' -- < how can I cast each value?
|| ' RETURNING uuid'
FROM jsonb_each_text(js);
IF _sql IS NOT NULL THEN
-- RAISE NOTICE '%', _sql; -- uncomment for debugging
EXECUTE _sql INTO new_id; -- comment out for debugging
END IF;
END
$func$ LANGUAGE plpgsql ;
您将值作为(未类型化的)字符串文字连接到查询字符串中,因此您不需要添加显式转换。Postgres 将强制每个值自动转换为目标列的数据类型。(当然,必须对该类型有效。否则会出错。但是显式强制转换不会得到相同的结果。)
您发布的功能按原样工作。我只建议
format()
整理外衣:请参阅欧文答案中更新的插入功能。我决定将插入与相关问题的更新功能合并:Update dynamic column names using keys from jsonb argument。如果提供了“id”,此函数将更新现有记录,或者如果未提供“id”,则生成新的 UUID 并插入新记录。两种方式都返回资源 ID。