Search code examples
c#.netpostgresql.net-coreplpgsql

Zero length delimited identifier error on postgresql function

I have a table, HVACLoads with JSON columns CoolingLoads and HeatingLoadsin PostgreSQL DB. I am just changing the properties of JSON using PostgreSQL JSON functions to update the inner properties by calling the function with migration builder during the Ef Core migration with c#. For example, the data structure for cooling and heating loads is below.

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

And below is the resulting structure

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

I am calling the below PostgreSQL function from migration and trying to update the properties.

  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;
");

Calling the above function using the below code inside the migration Up method:

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

But getting an error like

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

Could anyone please point me to why I am getting the above error?

Solution

  • Looks like you have escaped (doubled up) double-quotes one times too many. Try this as SQL string in double-quotes:

    "
CREATE OR REPLACE FUNCTION set_loads(object_name text, field_name text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   query text;
   cooling_power_value numeric;
   heating_power_value numeric;
BEGIN
   IF field_name = 'PercentOfTotal' THEN
      query := $q$
         UPDATE ""HvacLoadReports""
         SET    ""PercentOfTota"" = jsonb_set(""PercentOfTota"", 'Roof'
                              , (SELECT jsonb_object_agg(
                                             key
                                           , CASE WHEN key = 'PercentOfTotal'
                                                THEN value
                                                ELSE jsonb_set('{""power"": {""scalar"": 0, ""unit"": ""btuh""}}', '{power, scalar}', value)
                                             END)
                                 FROM   jsonb_each(""PercentOfTota"" -> 'Roof')))$q$;
   ELSE
      -- ... more
   END IF;

   -- RAISE NOTICE '%', query;  -- to debug
   EXECUTE query;
END
$func$;
"

    I simplified so that all values nested in the key 'Roof' are replaced with the verbose form - except when the key is 'PercentOfTotal'.

    Also note the use of dollar-quotes to preserve plain single-quotes. See:

    Your life with Postgres is easier with legal, lower-case, unquoted identifiers. See: