more.sql

--
-- 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;