resily
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
copy_objectives
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;