如何在PostgreSQL中存储随上下文变化的动态值？

问题背景 #

正在基于PostgreSQL构建TTRPG交互式角色表的关系型数据库，需要实现通用可扩展的效果存储框架。核心需求是：当角色使用某类消耗品时，动态将其max_base_position的一半（向下取整）加到current_position上，且最终值不超过max_base_position；同时支持未来用户自定义各类效果。当前effect表的magnitude字段为静态整数，无法实现随角色属性变化的动态计算。

核心调整思路 #

放弃用静态字段存储固定值，改用动态表达式存储+存储过程执行的方案，既满足当前需求，也能支撑未来扩展。

1. 调整Schema以支持动态效果 #

修改effect表，新增字段存储动态计算逻辑，兼容静态效果的同时支持自定义表达式：

-- 先删除原effect表（设计阶段可操作），或用ALTER TABLE新增字段
CREATE TABLE "effect" (
  "effect_id" uuid UNIQUE PRIMARY KEY NOT NULL,
  "name" varchar(64) NOT NULL,
  "description" varchar(4096),
  "position_cost" integer,
  "duration_timeframe" timeframe NOT NULL,
  "duration_amount" integer,
  "object_type_affected" object_type NOT NULL,
  "field_affected" varchar(64),
  "operation" varchar(10) NOT NULL, -- 用varchar替代枚举，适配更多操作类型
  "calculation_expression" text, -- 存储动态计算表达式（如'max_base_position / 2'）
  "magnitude" integer, -- 保留静态值字段，兼容无需动态计算的效果
  "active" bool NOT NULL
);

2. 实现存储过程处理效果执行 #

创建存储过程，封装「检查消耗品存在性→计算动态值→更新角色属性→移除消耗品」的完整逻辑：

CREATE OR REPLACE PROCEDURE use_consumable(
  p_character_id uuid,
  p_consumable_id uuid
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_inventory_id uuid;
  v_effect record;
  v_calculated_value integer;
  v_new_current_position integer;
  v_max_base_position integer;
BEGIN
  -- 获取角色的背包ID和基础位置最大值
  SELECT inventory, max_base_position
  INTO v_inventory_id, v_max_base_position
  FROM character
  WHERE character_id = p_character_id;

  -- 校验消耗品是否在角色背包中
  IF NOT EXISTS (
    SELECT 1 FROM inventory_consumables
    WHERE inventory = v_inventory_id AND consumable = p_consumable_id
  ) THEN
    RAISE EXCEPTION '消耗品不在角色背包中';
  END IF;

  -- 获取消耗品关联的效果
  SELECT e.*
  INTO v_effect
  FROM consumable_effect ce
  JOIN effect e ON ce.effect = e.effect_id
  WHERE ce.consumable = p_consumable_id;

  -- 计算动态效果值（优先使用表达式，否则用静态magnitude）
  IF v_effect.calculation_expression IS NOT NULL THEN
    -- 动态执行表达式计算，绑定变量避免SQL注入
    EXECUTE format('SELECT %s', replace(v_effect.calculation_expression, 'max_base_position', v_max_base_position::text))
    INTO v_calculated_value;
  ELSE
    v_calculated_value = v_effect.magnitude;
  END IF;

  -- 计算新的当前位置（不超过最大值）
  SELECT LEAST(current_position + v_calculated_value, max_base_position)
  INTO v_new_current_position
  FROM character
  WHERE character_id = p_character_id;

  -- 更新角色属性
  UPDATE character
  SET current_position = v_new_current_position
  WHERE character_id = p_character_id;

  -- 从背包移除消耗品
  DELETE FROM inventory_consumables
  WHERE inventory = v_inventory_id AND consumable = p_consumable_id;

END $$;

3. 插入测试数据 #

现在可以插入动态效果的测试数据：

INSERT INTO effect VALUES (
  gen_random_uuid(),
  'Restore half of base Position',
  '恢复角色一半的基础位置值（向下取整，不超过最大值）',
  0,
  'instantaneous',
  0,
  'character',
  'current_position',
  '+',
  'max_base_position / 2', -- 动态计算表达式
  NULL, -- 静态值留空
  true
) RETURNING *;

4. 调用存储过程执行效果 #

用户点击「使用」按钮时，在API中调用此存储过程即可完成整个流程：

CALL use_consumable('角色UUID', '消耗品UUID');

扩展建议 #

• 若需支持更复杂的效果（如多字段计算、条件判断），可将calculation_expression替换为jsonb字段，存储包含条件、计算逻辑、目标字段的完整配置
• 新增effect_type字段，区分静态效果、动态表达式效果、脚本效果等类型，便于后续扩展
• 存储过程中可添加事务控制，确保属性更新与消耗品移除的原子性

内容的提问来源于stack exchange，提问作者Cameron Chrobocinski