Postgres With statements build style with transaction

smslike.ru.sql

WITH avaliable_phones AS (
  SELECT
    pn.id as id,

    -- rank() over (partition by pn.id order by pn.id) as rank,
    count(pn.id) over (partition by pn.id order by pn.id) as phones_count,
  
    -- getprice(c.id, s.id) price,
    c.id as country_id,
    c.slug as country_slug,
    s.id as service_id,
    s.name as service_name,
    s.short_name as service_short_name,    
    pn.id as phone_number_id,
    pn.phone_number as phone_number,
    pn.owner_id as owner_id,
    po.id as  phone_operator_id,
    po.slug as phone_operator_slug,
    su.balance as su_balance
  from 
    phone_number pn,
    phone_number_available_services pan,
    country c,
    service s,
    phone_operator po,
    suser_customuser su
  where 
    su.balance > 0 and
  
    -- su.balance > getprice(c.id, s.id)*:in_numbers_count and
    -- su.balance >= getprice(c.id, s.id) and
    -- getprice(c.id, s.id) > 0 and
  
    pn.deactivated is null AND
    s.deactivated is null AND
    pn.deactivated is null AND
  
    pn.id = pan.phonenumber_id AND
    pan.service_id = s.id and
    pn.phone_operator_id = po.id AND
    po.country_id = c.id and

  
    s.has_forwarding = :in_has_forwarding and
    su.id = :in_suid and
    c.slug = :in_country and     
    (:in_operator = ''
    or 
    (po.slug != '' and po.slug = :in_operator))

  order by 2
  ),

----------------------
  -- берём только первую запись c подходящим сервисом (самый использованый номер)
  avaliable_phones2 AS (
  select 
    a.*
  from 
    avaliable_phones a

  -- отсев забаненных номеров по указанной службе
  LEFT JOIN banned_phone_number b
  on (b.user_id = :in_suid and
      a.id = b.id and 
      a.service_id = b.service_id)
  
  where
    b.id is NULL and
    a.service_short_name = :in_service_id and
    a.su_balance >= getprice(a.country_id, a.service_id)
  -- к-во записей для покупки, нужно допилить позже механизм рандомизации реселера кому отдать покупку
  --limit :in_count
  limit 1
  ),

----------------------
  now_time AS (
    select now() as now2
  ),

----------------------  
  updates_phones AS (
  insert into service_transaction(
    paid,
    sms_requested,
    deactivated,

    owner_id,
    phone_number_id,
    service_id,
    created,
    modified
    )

    select 
      now_time.now2,
      now_time.now2,
      now_time.now2 + :in_sms_receive_time * interval '1 minute',
      
      avaliable_phones2.owner_id,
      avaliable_phones2.id,
      avaliable_phones2.service_id,
      now_time.now2,
      now_time.now2
    from
      now_time,
      avaliable_phones2
   RETURNING id as service_transaction_id
  ),

  update_balance as (
    update suser_customuser
    set balance = balance-getprice(avaliable_phones2.country_id, avaliable_phones2.service_id)
    --set balance = 0-getprice(avaliable_phones2.country_id, avaliable_phones2.service_id)
    from 
      avaliable_phones2
    where 
      suser_customuser.id = avaliable_phones2.owner_id
    RETURNING *
  ),

----------------------
  -- не учитывает переадпресацыю
  update_available_services as (
  delete from 
    phone_number_available_services pnas
  USING 
    avaliable_phones2
  where 
    pnas.phonenumber_id = avaliable_phones2.phone_number_id and
    pnas.service_id = avaliable_phones2.service_id
  RETURNING pnas.id as update_available_services_deleted_id
  )

----------------------
  select 
    avaliable_phones2.*, 
    updates_phones.*,
    update_balance.balance,
    update_available_services.*

  from 
    avaliable_phones2, 
    updates_phones, 
    update_balance,
    update_available_services