get_tool_price_credits

Сигнатура: get_tool_price_credits("p_tool" "text", "p_feature" "text" DEFAULT 'basic'::"text", "p_params" "jsonb" DEFAULT '{}'::"jsonb") RETURNS numeric
Язык: sql
Security: DEFINER

Тело функции

declare
  base numeric;
  out_price numeric;
  rec record;  -- <— сюда будут попадать строки с factor
begin
  -- базовая цена
  select credits_per_job
  into base
  from public.tool_flat_prices
  where tool = lower(p_tool)
    and feature = lower(p_feature)
    and (effective_to is null or effective_to > now())
  order by effective_from desc
  limit 1;

  if base is null then
    raise exception 'PRICE_NOT_FOUND %/%', p_tool, p_feature;
  end if;

  out_price := base;

  -- применяем мультипликаторы из tool_price_multipliers по совпадающим p_params
  if p_params is not null then
    for rec in
      select m.factor
      from public.tool_price_multipliers m
      where m.tool = lower(p_tool)
        and m.feature = lower(p_feature)
        and (m.effective_to is null or m.effective_to > now())
        and exists (
          select 1
          from jsonb_each_text(p_params) as t(k,v)
          where t.k = m.param and t.v = m.value
        )
    loop
      out_price := out_price * rec.factor;
    end loop;
  end if;

  return round(out_price::numeric, 3);
end;