Insane.sql

DROP FUNCTION IF EXISTS adolescent_pivot_create();
CREATE OR REPLACE FUNCTION adolescent_pivot_create()
  RETURNS VOID AS $$
DECLARE
BEGIN
  EXECUTE format('create or replace view adolescent_program_pivot as SELECT *
FROM crosstab(
         ''WITH concept_uuid_name_mapping AS (
    SELECT
      c.uuid,
      c.name
    FROM concept c
      INNER JOIN form_element fe ON c.id = fe.concept_id
      INNER JOIN form_element_group feg ON fe.form_element_group_id = feg.id
      INNER JOIN form f ON feg.form_id = f.id
      INNER JOIN form_mapping fm ON f.id = fm.form_id
      INNER JOIN program p ON p.id = fm.entity_id
    WHERE fm.entity_id IS NOT NULL AND fm.observations_type_entity_id IS NOT NULL AND p.name = ''''Adolescent''''
    GROUP BY c.uuid, c.name
    ORDER BY 2)
SELECT
  i.uuid                                                  AS rowid,
  cunm1.name                                              AS attribute,
  coalesce(string_agg(c2.name, '''', ''''), c1.name, obs.value) AS value
FROM program_encounter pe1
  INNER JOIN program_enrolment pe ON pe1.program_enrolment_id = pe.id
  CROSS JOIN jsonb_each_text(pe.observations || pe1.observations) obs
  CROSS JOIN jsonb_to_array(obs.value) answer_list
  INNER JOIN individual i ON pe.individual_id = i.id
  INNER JOIN concept_uuid_name_mapping cunm1 ON cunm1.uuid = obs.key
  LEFT OUTER JOIN concept c1 ON c1.uuid = obs.value
  LEFT OUTER JOIN concept c2 ON c2.uuid = answer_list.value
  LEFT OUTER JOIN program p ON pe.program_id = p.id
WHERE p.name = ''''Adolescent''''
GROUP BY i.uuid, cunm1.name, obs.value, pe1.encounter_date_time, c1.name, obs.key
ORDER BY 1, 2'', ''SELECT
  c.name
FROM concept c
  INNER JOIN form_element fe ON c.id = fe.concept_id
  INNER JOIN form_element_group feg ON fe.form_element_group_id = feg.id
  INNER JOIN form f ON feg.form_id = f.id
  INNER JOIN form_mapping fm ON f.id = fm.form_id
  INNER JOIN program p ON p.id = fm.entity_id
WHERE fm.entity_id IS NOT NULL AND fm.observations_type_entity_id IS NOT NULL AND p.name = ''''Adolescent''''
group by c.name order by 1'')
  AS adolescent_program_pivot(row_name VARCHAR, %s)',
                 (SELECT string_agg('"' || v || '" varchar', ' ,')
                  FROM (
                         SELECT c.name AS v
                         FROM concept c
                           INNER JOIN form_element fe ON c.id = fe.concept_id
                           INNER JOIN form_element_group feg
                             ON fe.form_element_group_id = feg.id
                           INNER JOIN form f ON feg.form_id = f.id
                           INNER JOIN form_mapping fm ON f.id = fm.form_id
                           INNER JOIN program p ON p.id = fm.entity_id
                         WHERE
                           fm.entity_id IS NOT NULL AND
                           fm.observations_type_entity_id IS NOT NULL
                           AND
                           p.name = 'Adolescent'
                         GROUP BY c.name
                         ORDER BY 1) AS concept_list));
END;
$$
LANGUAGE plpgsql;
SELECT adolescent_pivot_create();
SELECT *
FROM adolescent_program_pivot;