PostgreSQL函数出现零长度分隔标识符错误求助

问题背景 #

我在PostgreSQL数据库中有一张名为HVACLoads的表，包含CoolingLoads和HeatingLoads两个JSON类型列，需要通过EF Core迁移的MigrationBuilder调用自定义PostgreSQL函数，修改JSON内部属性结构：

原始JSON结构：

{
  "Roof": {
    "Total": 0,
    "Latent": 23,
    "SensibleTotal": 0,
    "PercentOfTotal": 0,
    "SensibleDelayed": 1,
    "SensibleInstant": 0,
    "SensibleReturnAir": 0
  },
  ...
}

目标JSON结构：

{
  "Roof": {
    "Total": { "scalar": 0, "unit": "btuh"},
    "Latent": { "scalar": 23, "unit": "btuh"},
    "SensibleTotal": { "scalar": 0, "unit": "btuh"},
    "PercentOfTotal": 0,
    "SensibleDelayed": { "scalar": 1, "unit": "btuh"},
    "SensibleInstant": { "scalar": 0, "unit": "btuh"},
    "SensibleReturnAir": { "scalar": 0, "unit": "btuh"}
  },
  ...
}

编写的自定义函数及迁移调用代码如下：

protected override void Up(MigrationBuilder migrationBuilder)
{              
    var options = new DbContextOptionsBuilder<APIDbContext>()
                           .UseNpgsql(Startup.Configuration["ConnectionStrings:Postgres"])
                           .Options;
     using var ctx = new APIDbContext(options);
     ctx.Database.OpenConnection(); 

     migrationBuilder.Sql(@"
CREATE OR REPLACE FUNCTION set_loads(object_name text, field_name text)
RETURNS void AS
$$
DECLARE
  query text;
  cooling_power_value numeric;
  heating_power_value numeric;
BEGIN
  IF field_name = 'PercentOfTotal' THEN
    query := 'UPDATE """"HvacLoadReports""""
              SET ' || field_name || ' = ''' || PercentOfTotal::numeric || ''',
                  """"CoolingLoads"""" = jsonb_set(""""CoolingLoads"""", ''{""""''' || object_name || '''"""",""""''' || field_name || '''""""}'', to_jsonb((""""CoolingLoads""""->>''""""' || object_name || '""""''->>''""""' || field_name || '""""'')::numeric), false),
                  """"HeatingLoads"""" = jsonb_set(""""HeatingLoads"""", ''{""""''' || object_name || '''"""",""""''' || field_name || '''""""}'', to_jsonb((""""HeatingLoads""""->>''""""' || object_name || '""""''->>''""""' || field_name || '""""'')::numeric), false)';
  ELSE
    cooling_power_value := (""""CoolingLoads""""->>''""""' || object_name || '""""'',''""scalar""'')::numeric;
    heating_power_value := (""""HeatingLoads""""->>''""""' || object_name || '""""'',''""scalar""'')::numeric;
    query := 'UPDATE """"HvacLoadReports""""
              SET """"CoolingLoads"""" = jsonb_set(""""CoolingLoads"""", ''{""""''' || object_name || '''"""",""""''' || field_name || '''""""}'', to_jsonb((""""CoolingLoads""""->>''""""' || object_name || '""""''->>''""""' || field_name || '""""'')::numeric), false),
                  """"HeatingLoads"""" = jsonb_set(""""HeatingLoads"""", ''{""""''' || object_name || '''"""",""""''' || field_name || '''""""}'', to_jsonb((""""HeatingLoads""""->>''""""' || object_name || '""""''->>''""""' || field_name || '""""'')::numeric), false),
                  """"CoolingLoads"""" = jsonb_set(""""CoolingLoads"""", ''{""""''' || object_name || '''"""",""""power""""}'', ''{""""scalar"""": '' || cooling_power_value || '', """"unit"""": """"btuh""""}'', true),
                  """"HeatingLoads"""" = jsonb_set(""""HeatingLoads"""", ''{""""''' || object_name || '''"""",""""power""""}'', ''{""""scalar"""": '' || heating_power_value || '', """"unit"""": """"btuh""""}'', true)';
  END IF;
  EXECUTE query;
END
$$ LANGUAGE plpgsql;
");

    migrationBuilder.Sql($"SELECT set_loads('{Roof}', '{Total}');");
}

执行时触发错误：

zero-length delimited identifier at or near """"

错误原因分析 #

• 引号转义错误：PL/pgSQL字符串中，双引号仅需用两个双引号（""）转义，你使用了四个双引号（""""），会被解析为空分隔标识符，直接触发报错。
• 表名不一致：你描述的表是HVACLoads，但函数中写的是HvacLoadReports，属于笔误，会导致找不到目标表。
• JSON路径语法错误：jsonb_set的路径参数格式错误，嵌套多层引号拼接导致语法混乱，正确路径应为'{"object_name", "field_name"}'格式。
• ->>操作符误用：->>仅接受一个路径参数，你在赋值cooling_power_value时传入两个参数，语法完全错误。
• 动态SQL变量引用错误：PercentOfTotal是列名，直接在字符串中拼接会导致语法错误，还存在SQL注入风险。
• 函数调用变量问题：'{Roof}', '{Total}'中的Roof和Total如果是字符串字面量，应直接写为'Roof', 'Total'；如果是C#变量，需确保已正确定义。

修正后的方案 #

1. 修正自定义函数 #

CREATE OR REPLACE FUNCTION set_loads(object_name text, field_name text)
RETURNS void AS
$$
DECLARE
  query text;
BEGIN
  IF field_name = 'PercentOfTotal' THEN
    query := format(
      'UPDATE "HVACLoads"
       SET "CoolingLoads" = jsonb_set("CoolingLoads", ''{%s, %s}'', to_jsonb(("CoolingLoads"->%s->>%s)::numeric), false),
           "HeatingLoads" = jsonb_set("HeatingLoads", ''{%s, %s}'', to_jsonb(("HeatingLoads"->%s->>%s)::numeric), false)',
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name), quote_literal(field_name)
    );
  ELSE
    query := format(
      'UPDATE "HVACLoads"
       SET "CoolingLoads" = jsonb_set(jsonb_set("CoolingLoads", ''{%s, %s}'', to_jsonb(''{"scalar": '' || ("CoolingLoads"->%s->>%s)::text || '', "unit": "btuh"}''::jsonb), false), ''{%s, "power"}'', to_jsonb(''{"scalar": '' || ("CoolingLoads"->%s->>%s)::text || '', "unit": "btuh"}''::jsonb), true),
           "HeatingLoads" = jsonb_set(jsonb_set("HeatingLoads", ''{%s, %s}'', to_jsonb(''{"scalar": '' || ("HeatingLoads"->%s->>%s)::text || '', "unit": "btuh"}''::jsonb), false), ''{%s, "power"}'', to_jsonb(''{"scalar": '' || ("HeatingLoads"->%s->>%s)::text || '', "unit": "btuh"}''::jsonb), true)',
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name),
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name), quote_literal(field_name),
      quote_literal(object_name),
      quote_literal(object_name), quote_literal(field_name)
    );
  END IF;
  EXECUTE query;
END
$$ LANGUAGE plpgsql;

• 使用format函数和quote_literal处理字符串拼接，避免引号转义错误和SQL注入。
• 修正表名为HVACLoads。
• 正确组合->（获取JSON对象）和->>（获取字符串值）操作符，构建合法的JSON路径。
• 直接构建目标JSON对象字符串并转换为jsonb类型赋值。

2. 修正函数调用 #

如果是字符串字面量调用：

migrationBuilder.Sql("SELECT set_loads('Roof', 'Total');");

如果使用C#变量：

var objectName = "Roof";
var fieldName = "Total";
migrationBuilder.Sql($"SELECT set_loads('{objectName}', '{fieldName}');");

额外注意事项 #

• 确保CoolingLoads和HeatingLoads列是jsonb类型（比json更适合修改操作），若为json类型需先转换为jsonb。
• 迁移前建议单独在数据库中测试函数逻辑，避免迁移失败导致数据异常。

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