github.com/benoitkugler/goacve@v0.0.0-20201217100549-151ce6e55dc8/server/migration/v2_to_v3/option_prix_insc.sql (about)

     1  -- -- modification de l'option JOUR
     2  CREATE OR REPLACE FUNCTION compute_jours (jours jsonb, date_debut timestamp)
     3      RETURNS integer[]
     4      AS $$
     5  DECLARE
     6      plage_from timestamp;
     7      plage_to timestamp;
     8      index_from int;
     9      index_to int;
    10  BEGIN
    11      IF jours IS NULL THEN
    12          RETURN '{}';
    13      END IF;
    14      IF coalesce(jours ->> 'from', '') = '' OR coalesce(jours ->> 'to', '') = '' THEN
    15          RETURN '{}';
    16      END IF;
    17      plage_from := to_date(jours ->> 'from', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
    18      plage_to := to_date(jours ->> 'to', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
    19      IF coalesce(plage_from, '0001-01-01T00:00:00Z') = '0001-01-01T00:00:00Z' OR coalesce(plage_to, '0001-01-01T00:00:00Z') = '0001-01-01T00:00:00Z' THEN
    20          RETURN '{}';
    21      END IF;
    22      index_from := extract(DAYS FROM plage_from - date_debut);
    23      index_to := extract(DAYS FROM plage_to - date_debut);
    24      RETURN ARRAY (
    25          SELECT
    26              a.n
    27          FROM
    28              generate_series(index_from, index_to) AS a (n));
    29  END;
    30  $$
    31  LANGUAGE plpgsql;
    32  
    33  UPDATE
    34      inscriptions
    35  SET
    36      participants = (
    37          SELECT
    38              jsonb_agg(
    39                  CASE WHEN jsonb_typeof(value -> 'options_prix' -> 'jour') = 'object' THEN
    40                      jsonb_set(value, '{options_prix, jour}', to_jsonb (compute_jours (value -> 'options_prix' -> 'jour', (
    41                                      SELECT
    42                                          date_debut FROM camps
    43                                      WHERE
    44                                          id = (value -> 'id_camp')::int))))
    45                      ELSE
    46                          value
    47                  END)
    48          FROM
    49              jsonb_array_elements(participants));
    50  
    51  DROP FUNCTION compute_jours (jours jsonb, date_debut timestamp);
    52