Created
April 25, 2019 16:07
-
-
Save jjn1056/91e0966a4954c3d39fbd48b0564f4548 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- For all faculty (active and inactive) give them a grandfather license | |
-- and assign them as the owner. Basically the grandfather license is a license | |
-- to just keep doing what they already can do. It however has an expiration | |
-- date | |
/* 'faculty' is a set of everyone in the person table that is type faculty */ | |
with faculty as ( | |
select person_id | |
from person | |
where person_type='faculty'), | |
/* insert into license once for each item in the faculty set */ | |
grandfather_license as ( | |
insert into license(kind, expires_on, valid_from) | |
select 'grandfather', '2017-06-30', '2016-07-01' from faculty | |
returning license_id ) | |
insert into license_person( | |
person_id, | |
license_id) | |
/* We need to manufactor a join column here, since the faculty and new_license | |
sets are identical in length we can just use a row index. I don't actually | |
care which license is assign to which instructor since at this stage its all | |
the same type of license. */ | |
select person_id, license_id from | |
( select license_id, row_number() over(order by license_id) as index from grandfather_license) as a, | |
(select person_id, row_number() over(order by person_id) as index from faculty) as b | |
where a.index = b.index; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment