Изменение странного запроса Mysql на Postgresql, который использует group_concat

Я перемещаю нашу серверную базу данных с mysql на postgres и нахожусь в процессе переноса всех наших старых запросов/функций. Большинство из них тривиальны, но сегодня я наткнулся на один, который заставил меня почесать голову. Вот:

UPDATE
  k_coderound AS cr, k_coderound AS cr_m
SET
  cr.is_correct = IF(
    (SELECT GROUP_CONCAT(option_id ORDER BY variable_id) AS `values` FROM k_value    
         WHERE code_round_id=cr.id GROUP BY code_round_id) = 
    (SELECT GROUP_CONCAT(option_id ORDER BY variable_id) AS `values` FROM k_value 
         WHERE code_round_id=cr_m.id GROUP BY code_round_id),
    1,
    0
  )
WHERE     
 cr.is_master=0 
 AND cr_m.is_master=1 
 AND cr_m.object_id=cr.object_id 
 AND cr_m.content_type_id =cr.content_type_id

Я знаю, что в Postgres нет group_concat, и вместо этого следует использовать array_agg. Моя проблема в том, что я не могу понять, что именно происходит - этот запрос был написан много лет назад кем-то, кого с нами больше нет. Также это усложняет отсутствие оператора IF в Postgres. Если кто-то может дать отзыв или совет, буду очень признателен!


mysql postgresql database-migration group-concat
person the_man_slim    schedule 12.06.2012    source источник
comment
Можете ли вы указать, к какой таблице относятся столбцы option_id , variable_id и code_round_id? Они принадлежат k_value?   -  person ypercubeᵀᴹ    schedule 12.06.2012
comment
option_id, variable_id и code_round_id принадлежат k_value   -  person the_man_slim    schedule 12.06.2012
comment
IF обычно преобразуется в CASE в PostgreSQL (и любой другой стандартной базе данных SQL)   -  person a_horse_with_no_name    schedule 12.06.2012

Ответы (1)


arrow_upward
1
arrow_downward

Трудно сказать, каково намерение. Мой подход к этому заключается в том, чтобы сначала найти оператор SELECT, который возвращает «целевые» данные.

Что-то вроде этого:

select cr.is_correct,
       array_agg(case when kv1.code_round_id = cr.id then kv1.option_id else null end, ',' order by kv1.variable_id) as kv_values1,
       array_agg(case when kv2.code_round_id = cr_m.id then kv2.option_id else null end, ',' order by kv2.variable_id) as kv_values2
from k_coderound cr
  join k_value kv1 on kv1.code_round_id = cr.id
  join k_coderound cr_m 
       on cr_m.object_id=cr.object_id 
      and cr_m.content_type_id =cr.content_type_id 
  join k_value kv2 on kv2.code_round_id = cr_m.id
where cr.is_master=0 
  and cr_m.is_master=1

Это, скорее всего, неверно, но я думаю, что это показывает, как нестандартные выражения MySQL могут быть переведены в стандартный SQL (и, следовательно, в PostgreSQL).

Как только это кажется правильным, я бы обернул это в инструкцию UPDATE:

update k_coderound cru
  set cr.is_correct = case 
                        when t.kv_values1 = t.kv_values2 then 1 
                        else 0 
                      end
from (select cr.ctid, 
             array_agg(case when kv1.code_round_id = cr.id then kv1.option_id else null end, ',' order by kv1.variable_id) as kv_values1,
             array_agg(case when kv2.code_round_id = cr_m.id then kv2.option_id else null end, ',' order by kv2.variable_id) as kv_values2
    from k_coderound cr
      join k_value kv1 on kv1.code_round_id = cr.id
      join k_coderound cr_m 
           on cr_m.object_id=cr.object_id 
          and cr_m.content_type_id =cr.content_type_id 
      join k_value kv2 on kv2.code_round_id = cr_m.id
    where cr.is_master=0 
      and cr_m.is_master=1 
) t
where t.ctid = cru.ctid

Я почти уверен, что пропустил некоторые моменты синтаксиса, но, надеюсь, это поможет вам начать работу.

person a_horse_with_no_name    schedule 12.06.2012