Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 => {
select => [qw /p.person p.email/],
from => [
{ person => { -as => 'p' } },
],
where => {
'p.person_type' => 'faculty',
'p.person_status' => { -ne => 'pending' },
'p.default_license_id' => 1,
},
},
},
grandfather => {
-insert => {
into => 'license(kind, expires_on, valid_from)',
select => {
select => [\'grandfather', \'2017-06-30', \'2016-07-01'],
from => 'faculty',
},
returning => 'license_id',
}
},
],
into => 'license_person(person_id, license_id)',
select => {
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment