PostgreSQL中如何用JSON替代hstore，通过NEW #=创建列变量？

问题背景 #

原本使用hstore扩展实现触发器中对NEW行的指定列赋值，代码如下：

temp_sql_string:='"'||p_k||'"=>"'||nextval(seq::regclass)||'"';
NEW := NEW #= temp_sql_string :: public.hstore;

尝试改用JSON实现相同逻辑，以下代码无法生效：

temp_sql_string:='"'||p_k||'":"'||nextval(seq::regclass)||'"';          
NEW := NEW #= temp_sql_string::json;

解决方案 #

核心问题说明 #

1. #=是hstore专属的赋值操作符，JSON/JSONB类型不支持该操作符
2. 直接拼接字符串生成的是JSON片段（缺少外层{}），不是合法的完整JSON对象，无法直接用于行类型赋值

正确实现代码（推荐用JSONB） #

利用jsonb_set函数结合行与JSONB的互转来实现，代码示例：

DECLARE
  next_val bigint := nextval(seq::regclass); -- 先获取序列值，避免字符串拼接风险
BEGIN
  -- 将NEW转为JSONB，修改指定键值后再转回原表行类型
  NEW := jsonb_set(
           to_jsonb(NEW),
           ARRAY[p_k], -- 指定要修改的列名（键）
           to_jsonb(next_val)
         )::your_table_name%rowtype; -- 替换为实际表名
  RETURN NEW;
END;

补充说明 #

• 优先使用JSONB而非JSON：JSONB支持更多操作函数，性能更优，且能更好地处理复杂结构
• 避免字符串拼接生成JSON：直接用to_jsonb转换值可避免格式错误（比如列名含特殊字符的场景）
• 如果必须使用JSON类型，只需将代码中的jsonb_set替换为json_set，to_jsonb替换为to_json即可

内容的提问来源于stack exchange，提问作者Jc John