gistfile1.txt

use strict;
use warnings;

use Test::More;
use Test::Warn;
use Test::Exception;
use SQL::Abstract::Test import => [qw(is_same_sql_bind diag_where dumper) ];
use SQL::Abstract;
use SQL::Abstract::ExtraClauses;

ok my $sqlac = SQL::Abstract::ExtraClauses->new(unknown_unop_always_func => 1);

{
=over

    with faculty as (
    select p.person_id, p.email                                                   
    from person p   
    where person_type='faculty'
    and person_status != 'pending'
    and default_license_id IS NULL
    ),
    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)
    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;

=cut

  my $sql = $sqlac->insert({
    with => [ 
      faculty => {
        -select => {
          _ => [qw /p.person p.email/],
          from => [ person => -as => 'p' ],
          where => {
            'p.person_type' => 'faculty',
            'p.person_status' => { '!=' => 'pending' },
            'p.default_license_id' => undef,
          },
        },
      },
      grandfather => {
        -insert => {
          into => 'license',
          fields => [ qw(kind expires_on valid_from) ],
          select => {
            select => [\(qw('grandfather' '2017-06-30' '2016-07-01'))], 
            from => 'faculty',
          },
          returning => 'license_id',
        }
      },
    ],
    into => 'license_person',
    fields => [ qw(person_id, license_id) ],
    select => {
      _ => ['person_id', 'license_id'],
      from => ['grandfather'],
      where => {
        'a.index' => { -ident => 'b.index' },
      },
    },
  });

  warn $sql;
}

done_testing;

__END__

$sql is

INSERT INTO license_person(person_id, license_id) SELECT person_id, license_id FROM grandfather WHERE a.index = b.index