77 lines
2.1 KiB
PL/PgSQL
77 lines
2.1 KiB
PL/PgSQL
-- Kompaktní JSON pro diagnostiku jednoho planning_run (MCP / UI).
|
|
|
|
create or replace function ems.fn_planning_run_debug(p_run_id int)
|
|
returns jsonb
|
|
language plpgsql
|
|
stable
|
|
as $fn$
|
|
declare
|
|
r_run ems.planning_run%rowtype;
|
|
v_intervals jsonb;
|
|
v_first_charge timestamptz;
|
|
v_first_bat_export timestamptz;
|
|
v_top_sell jsonb;
|
|
begin
|
|
select * into r_run from ems.planning_run where id = p_run_id;
|
|
if not found then
|
|
return null::jsonb;
|
|
end if;
|
|
|
|
select coalesce(jsonb_agg(to_jsonb(pi.*) order by pi.interval_start), '[]'::jsonb)
|
|
into v_intervals
|
|
from ems.planning_interval pi
|
|
where pi.run_id = p_run_id;
|
|
|
|
select pi.interval_start
|
|
into v_first_charge
|
|
from ems.planning_interval pi
|
|
where pi.run_id = p_run_id
|
|
and coalesce(pi.battery_setpoint_w, 0) > 500
|
|
order by pi.interval_start
|
|
limit 1;
|
|
|
|
select pi.interval_start
|
|
into v_first_bat_export
|
|
from ems.planning_interval pi
|
|
where pi.run_id = p_run_id
|
|
and coalesce(pi.battery_setpoint_w, 0) < -500
|
|
and coalesce(pi.grid_setpoint_w, 0) < 0
|
|
order by pi.interval_start
|
|
limit 1;
|
|
|
|
select coalesce(
|
|
jsonb_agg(
|
|
jsonb_build_object(
|
|
'interval_start', x.interval_start,
|
|
'effective_sell_price', x.effective_sell_price
|
|
)
|
|
order by x.effective_sell_price desc nulls last
|
|
),
|
|
'[]'::jsonb
|
|
)
|
|
into v_top_sell
|
|
from (
|
|
select pi.interval_start, pi.effective_sell_price
|
|
from ems.planning_interval pi
|
|
where pi.run_id = p_run_id
|
|
order by pi.effective_sell_price desc nulls last
|
|
limit 3
|
|
) x;
|
|
|
|
return jsonb_build_object(
|
|
'planning_run', to_jsonb(r_run),
|
|
'solver_params', r_run.solver_params,
|
|
'intervals', v_intervals,
|
|
'summary', jsonb_build_object(
|
|
'first_charge_slot', to_jsonb(v_first_charge),
|
|
'first_battery_export_slot', to_jsonb(v_first_bat_export),
|
|
'top_sell_slots', v_top_sell,
|
|
'solver_params_version', r_run.solver_params->'version'
|
|
)
|
|
);
|
|
end;
|
|
$fn$;
|
|
|
|
comment on function ems.fn_planning_run_debug(int) is
|
|
'Jeden jsonb: metadata planning_run, solver_params, všechny planning_interval řádky a krátký summary.';
|