resily Database

Parameters

Name	Type	Mode
organization_id	bigint	IN
old_okr_term_id	bigint	IN
okr_node_id_mapping	USER-DEFINED	IN

Definition

<<copy_objectives>>
DECLARE
  old_objective RECORD;
  new_objective RECORD;
  new_node_id int8;
  mapping hstore DEFAULT '';
BEGIN
  FOR old_objective IN
    SELECT objectives.*
    FROM
      objectives
      INNER JOIN okr_nodes
        ON okr_nodes.id = objectives.okr_node_id
        AND okr_nodes.okr_term_id = old_okr_term_id
      WHERE objectives.disabled_at IS NULL
  LOOP
    EXECUTE
      $SQL$
        INSERT INTO objectives
          ("name", "description", "progress_rate", "organization_id", "okr_node_id", "created_at", "updated_at", "disabled_at", "use_weighting")
        VALUES
          ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        RETURNING *;
      $SQL$
      INTO new_objective
      USING old_objective.name, old_objective.description, old_objective.progress_rate, organization_id, (okr_node_id_mapping -> old_objective.okr_node_id::text)::int8, old_objective.created_at, old_objective.updated_at, old_objective.disabled_at, old_objective.use_weighting;

-- TODO テストデータがない
    -- TODO GCP上のデータもコピーしたほうが良さそう
    EXECUTE
      $SQL$
        INSERT INTO objective_attachments
          ("organization_id", "objective_id", "name", "file_path", "created_at", "updated_at")
        SELECT
          $2, $3, oa.name, oa.file_path, oa.created_at, oa.updated_at
        FROM objective_attachments AS oa
        WHERE objective_id = $1
        RETURNING *;
      $SQL$
      USING old_objective.id, organization_id, new_objective.id;

-- objective_members のコピー
    EXECUTE
      $SQL$
        INSERT INTO objective_members
          ("organization_id", "objective_id", "role", "user_id", "created_at", "updated_at")
        SELECT
          $2, $3, om.role, om.user_id, om.created_at, om.updated_at
        FROM objective_members AS om
        WHERE objective_id = $1
        RETURNING *;
      $SQL$
      USING old_objective.id, organization_id, new_objective.id;

-- objective id のmappingを作成
    mapping := mapping || hstore(old_objective.id::text, new_objective.id::text);
  END LOOP;

RETURN mapping;
END;