英文:
Zero length delimited identifier error on postgresql function
问题
I have a table, HVACLoads with JSON columns CoolingLoads and HeatingLoads in 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},// ... (other properties)}
And below is the resulting structure
{"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"}},// ... (other properties)}
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$$DECLAREquery text;cooling_power_value numeric;heating_power_value numeric;BEGINIF field_name = 'PercentOfTotal' THENquery := 'UPDATE ""HvacLoadReports"" SET ' || field_name || ' = ''' || PercentOfTotal::numeric || ''',""CoolingLoads"" = jsonb_set(""CoolingLoads"", '''{' || object_name || '}'',''' || field_name || '''}''::jsonb, (""CoolingLoads""->>'{' || object_name || '}->>' || field_name || ''')::numeric, false),""HeatingLoads"" = jsonb_set(""HeatingLoads"", '''{' || object_name || '}'',''' || field_name || '''}''::jsonb, (""HeatingLoads""->>'{' || object_name || '}->>' || field_name || ''')::numeric, false)';ELSEcooling_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 || '''}''::jsonb, (''{' || cooling_power_value || ',''btuh''}'')::jsonb, true),""HeatingLoads"" = jsonb_set(""HeatingLoads"", '''{' || object_name || '}'',''' || field_name || '''}''::jsonb, (''{' || heating_power_value || ',''btuh''}'')::jsonb, true)';END IF;EXECUTE query;END$$ LANGUAGE plpgsql;");Calling the above function using the below code inside the migration `Up` method:```csharpmigrationBuilder.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?
英文:
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$$DECLAREquery text;cooling_power_value numeric;heating_power_value numeric;BEGINIF field_name = 'PercentOfTotal' THENquery := '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)';ELSEcooling_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?
答案1
得分: 0
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 voidLANGUAGE plpgsql AS$func$DECLAREquery text;cooling_power_value numeric;heating_power_value numeric;BEGINIF field_name = 'PercentOfTotal' THENquery := $q$UPDATE "HvacLoadReports"SET "PercentOfTotal" = jsonb_set("PercentOfTotal", 'Roof', (SELECT jsonb_object_agg(key, CASE WHEN key = 'PercentOfTotal'THEN valueELSE jsonb_set('{"power": {"scalar": 0, "unit": "btuh"}}', '{power, scalar}', value)END)FROM jsonb_each("PercentOfTotal" -> 'Roof')))$q$;ELSE-- ... moreEND IF;-- RAISE NOTICE '%', query; -- to debugEXECUTE 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:
英文:
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 voidLANGUAGE plpgsql AS$func$DECLAREquery text;cooling_power_value numeric;heating_power_value numeric;BEGINIF field_name = 'PercentOfTotal' THENquery := $q$UPDATE ""HvacLoadReports""SET ""PercentOfTota"" = jsonb_set(""PercentOfTota"", 'Roof', (SELECT jsonb_object_agg(key, CASE WHEN key = 'PercentOfTotal'THEN valueELSE jsonb_set('{""power"": {""scalar"": 0, ""unit"": ""btuh""}}', '{power, scalar}', value)END)FROM jsonb_each(""PercentOfTota"" -> 'Roof')))$q$;ELSE-- ... moreEND IF;-- RAISE NOTICE '%', query; -- to debugEXECUTE 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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论