update.sql

WITH
paranoid_locking AS (
  SELECT 1
  FROM cents_hax
  WHERE
    id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
  FOR UPDATE
),
new_hax AS (
  SELECT
    cent_id,
    NULLIF(
      COALESCE(jsonb_object_agg(hax_join.key, hax_join.group_data) FILTER (WHERE hax_join.key IS NOT NULL), '{}'::jsonb) ||
      COALESCE(haxlect(hax_join.row_data), '{}'::jsonb),
      '{}'::jsonb
    ) AS hax
  FROM (
    SELECT
      id AS cent_id,
      null::text AS key,
      null::jsonb AS group_data,
      null::jsonb AS row_data
    FROM cents_hax
    WHERE
      id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
    UNION ALL
    SELECT
      cent_id AS cent_id,
      'iv_' || firo_id AS key,
      jsonb_strip_nulls(
        jsonb_build_object('count', count(*), 'first', min(date), 'last', max(date))
      ) AS group_data,
      jsonb_object_agg('mf_' || dang_id, true) FILTER (WHERE dang_id IS NOT NULL) AS row_data
    FROM investments
    WHERE
      cent_id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
    GROUP BY cent_id, firo_id
    UNION ALL
    SELECT
      cent_id AS cent_id,
      'rc_' || firo_id AS key,
      jsonb_strip_nulls(
        jsonb_build_object('count', count(*), 'first', min(date), 'last', max(date))
      ) AS group_data,
      NULLIF(
        COALESCE(jsonb_object_agg('mf_' || dang_id, true) FILTER (WHERE dang_id IS NOT NULL), '{}'::jsonb) ||
        COALESCE(jsonb_object_agg('dl_' || ryti_id, true) FILTER (WHERE ryti_id IS NOT NULL), '{}'::jsonb),
        '{}'::jsonb
      ) AS row_data
    FROM poros
    WHERE
      cent_id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
    GROUP BY cent_id, firo_id
    UNION ALL
    SELECT
      cent_id AS cent_id,
      'rs_' || firo_id AS key,
      jsonb_strip_nulls(
        jsonb_build_object('count', count(*), 'first', min(date), 'last', max(date))
      ) AS group_data,
      jsonb_object_agg('mf_' || dang_id, true) FILTER (WHERE dang_id IS NOT NULL) AS row_data
    FROM liras
    WHERE
      cent_id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
    GROUP BY cent_id, firo_id
    UNION ALL
    SELECT
      cent_id AS cent_id,
      'rj_' || firo_id AS key,
      jsonb_strip_nulls(
        jsonb_build_object('count', count(*), 'first', min(date), 'last', max(date))
      ) AS group_data,
      jsonb_object_agg('mf_' || dang_id, true) FILTER (WHERE dang_id IS NOT NULL) AS row_data
    FROM simis
    WHERE
      cent_id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
    GROUP BY cent_id, firo_id
    UNION ALL
    SELECT * FROM (
      SELECT
        cent_id AS cent_id,
        null::text AS key,
        null::jsonb AS group_data,
        (
          ('{"mf_' || dang_id || '": true}')::jsonb ||
          ('{"pd_' || firo_id || '": true}')::jsonb ||
          -- because apsos.d is still json not jsonb
          COALESCE((SELECT json_object_agg('pd_' || firo_id || '_' || key, value) FROM json_each(d))::jsonb, '{}'::jsonb)
        ) AS row_data
      FROM apsos
      WHERE
        cent_id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
      ORDER BY created_at
    ) p1
    UNION ALL
    SELECT
      cent_id AS cent_id,
      null::text AS key,
      null::jsonb AS group_data,
      (
        ('{"sp_' || firo_id || '": true}')::jsonb ||
        ('{"mf_' || dang_id || '": true}')::jsonb
      ) AS row_data
    FROM weros
    WHERE
      cent_id BETWEEN '0e000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
  ) AS hax_join
  GROUP BY cent_id
)
UPDATE cents_hax
SET
  hax = new_hax.hax,
  hax_updated_at = NOW()
FROM
  new_hax
WHERE
  cents_hax.id = new_hax.cent_id