Zero length delimited identifier error on postgresql function

Question

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?

`""""` in various places should be `""`, although quite why you need to quote those columns I don't know, I don't think it's necessary — Charlieface, Apr 16 '23 at 22:45
I am using the PostgreSQL database and I am modifying the table column properties I thought PostgreSQL won't allow me if we don't quote the columns — kumar425, Apr 16 '23 at 22:48
@Charlieface, Could you please let me know, where do I need to replace the quotes `""""` to `""` — kumar425, Apr 16 '23 at 22:53
Like I said: you don't need to, they only need quoting if you have names like `FROM` or `SELECT`, or weird characters like a space or a `'`. If you want to keep them, change all of them to `""` in other words you need to double them up from the C# side, you don't need to double up from the Postgres side, so you need only 2 not 4. — Charlieface, Apr 17 '23 at 00:23

Erwin Brandstetter · Accepted Answer · 2023-06-19T21:17:28.430

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:

Insert text with single quotes in PostgreSQL

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

Are PostgreSQL column names case-sensitive?

Thank you, It looks like you are directly updating the column `PercentOfTota`, but it's not the direct column in `HvacLoadReports`; instead, it's just a property in the JSON column like `CoolingLoads` -> `Roof` -> `PercentOfTota`. Can you please fill the `else` block, like pseudo code? — kumar425, Apr 17 '23 at 15:00
@kumar425: I have done enough. SO is a Q/A site, not a code writing service, no offense. — Erwin Brandstetter, Apr 17 '23 at 15:35
Thank you, this way, you are updating the inner JSON object like updating direct column `SET ""PercentOfTota"" = jsonb_set(""PercentOfTota"", 'Roof'` — kumar425, Apr 17 '23 at 15:38
I am doing exactly what your original tried to do, except for the noted differences. Un-comment the `RAISE NOTICE` line I added, and comment the `EXECUTE` line to debug the generated SQL. — Erwin Brandstetter, Apr 17 '23 at 16:08

Zero length delimited identifier error on postgresql function

1 Answers1