File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
create table contact (
id bigserial primary key,
category varchar(20) not null, -- 'APPLICANT' | 'MJ' | 'LAWYER'
name varchar(100) not null,
affiliation varchar(200), -- 소속 (신청기관 담당자는 기관명이 들어감)
dept_name varchar(200),
title varchar(100),
phone varchar(50),
email varchar(200),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
alter table organization
add column applicant_contact_id bigint references contact (id) on delete set null,
add column mj_contact_id bigint references contact (id) on delete set null,
add column lawyer_contact_id bigint references contact (id) on delete set null;
-- 백필 1/3: 신청기관 담당자. 기관 행마다 최대 1명이므로 기관 id로 바로 correlate 한다.
do $$
declare
org_row record;
new_contact_id bigint;
begin
for org_row in
select id, org_name, dept_name, manager_name, manager_title, manager_phone, manager_email
from organization
where manager_name is not null
loop
insert into contact (category, name, affiliation, dept_name, title, phone, email)
values ('APPLICANT', org_row.manager_name, org_row.org_name, org_row.dept_name,
org_row.manager_title, org_row.manager_phone, org_row.manager_email)
returning id into new_contact_id;
update organization set applicant_contact_id = new_contact_id where id = org_row.id;
end loop;
end $$;
-- 백필 2/3: 문정원 담당자. 여러 기관이 같은 사람을 공유할 수 있어 distinct 튜플당 1행만 만든다.
do $$
declare
mj_row record;
new_contact_id bigint;
begin
for mj_row in
select distinct mj_manager_name, mj_dept_name, mj_manager_phone, mj_manager_email
from organization
where mj_manager_name is not null
loop
insert into contact (category, name, dept_name, phone, email)
values ('MJ', mj_row.mj_manager_name, mj_row.mj_dept_name,
mj_row.mj_manager_phone, mj_row.mj_manager_email)
returning id into new_contact_id;
update organization
set mj_contact_id = new_contact_id
where mj_manager_name = mj_row.mj_manager_name
and mj_dept_name is not distinct from mj_row.mj_dept_name
and mj_manager_phone is not distinct from mj_row.mj_manager_phone
and mj_manager_email is not distinct from mj_row.mj_manager_email;
end loop;
end $$;
-- 백필 3/3: 담당 변호사. 배정일은 사람이 아니라 기관↔변호사 관계에 속하므로 organization에 남긴다.
do $$
declare
lawyer_row record;
new_contact_id bigint;
begin
for lawyer_row in
select distinct lawyer_name, lawyer_phone, lawyer_email
from organization
where lawyer_name is not null
loop
insert into contact (category, name, phone, email)
values ('LAWYER', lawyer_row.lawyer_name, lawyer_row.lawyer_phone, lawyer_row.lawyer_email)
returning id into new_contact_id;
update organization
set lawyer_contact_id = new_contact_id
where lawyer_name = lawyer_row.lawyer_name
and lawyer_phone is not distinct from lawyer_row.lawyer_phone
and lawyer_email is not distinct from lawyer_row.lawyer_email;
end loop;
end $$;
alter table organization
drop column dept_name,
drop column manager_name,
drop column manager_title,
drop column manager_phone,
drop column manager_email,
drop column mj_dept_name,
drop column mj_manager_name,
drop column mj_manager_phone,
drop column mj_manager_email,
drop column lawyer_name,
drop column lawyer_phone,
drop column lawyer_email;