Sha256: e94d6e34669da322ee5c86de4881e5c7d31a72ae88964b224e23a5c9e9a80f7c

Contents?: true

Size: 1.98 KB

Versions: 6

Compression:

Stored size: 1.98 KB

Contents

class CreateFnToRefreshCurrentObs < ActiveRecord::Migration[5.1]
  def up
    sql = <<-SQL
      CREATE OR REPLACE FUNCTION refresh_current_observation_set(a_patient_id integer)
        -- Function to update the pathology_current_observation_set for a patient.
        -- It stores the most recent results into the jsonb hash on that table.
        -- To run for all patients, use
        --   select refresh_current_observation_set(id) from patients;
        --
          RETURNS integer
          LANGUAGE 'plpgsql'
          as $$
        BEGIN
        with current_patient_obs as(
            select
              DISTINCT ON (p.id, obxd.id)
        p.id as patient_id,
              obxd.code,
              json_build_object('result',(obx.result),'observed_at',obx.observed_at) as value
              from patients p
              inner join pathology_observation_requests obr on obr.patient_id = p.id
              inner join pathology_observations obx on obx.request_id = obr.id
              inner join pathology_observation_descriptions obxd on obx.description_id = obxd.id
              where p.id = a_patient_id
              order by p.id, obxd.id, obx.observed_at desc
          ),
          current_patient_obs_as_jsonb as (
            select patient_id,
              jsonb_object_agg(code, value) as values,
              CURRENT_TIMESTAMP,
              CuRRENT_TIMESTAMP
              from current_patient_obs
              group by patient_id order by patient_id
          )
          insert into pathology_current_observation_sets (patient_id, values, created_at, updated_at)
            select * from current_patient_obs_as_jsonb
            ON conflict (patient_id)
            DO UPDATE
            SET values = excluded.values, updated_at = excluded.updated_at;
        RETURN a_patient_id;
      END
      $$;
    SQL
    ActiveRecord::Base.connection.execute(sql)
  end

  def down
    ActiveRecord::Base.connection.execute("drop function if exists refresh_current_observation_set(integer)")
  end
end

Version data entries

6 entries across 6 versions & 1 rubygems

Version Path
renalware-core-2.0.0.pre.rc8 db/migrate/20171213111513_create_fn_to_refresh_current_obs.rb
renalware-core-2.0.0.pre.rc7 db/migrate/20171213111513_create_fn_to_refresh_current_obs.rb
renalware-core-2.0.0.pre.rc6 db/migrate/20171213111513_create_fn_to_refresh_current_obs.rb
renalware-core-2.0.0.pre.rc5 db/migrate/20171213111513_create_fn_to_refresh_current_obs.rb
renalware-core-2.0.0.pre.rc4 db/migrate/20171213111513_create_fn_to_refresh_current_obs.rb
renalware-core-2.0.0.pre.rc3 db/migrate/20171213111513_create_fn_to_refresh_current_obs.rb