view0 full outer join view1 on view0.size = view1.size and view0.buffersize = view1.buffersize full outer join view2 on view1.size = view2.size and view1.buffersize = view2.buffersize full outer join view3 on view2.size = view3.size and view2.buffersize = view3.buffersize #### use strict my $last = 'view0'; print join(' full outer join ', 'view0', view_on_clauses()),"\n"; sub view_on_clauses { return map { join(' ', $_, 'on', and_clause($_)) } qw(view1 view2 view3); } sub and_clause { my($view) = @_; return join(' and ', equality_clauses($view)); } sub equality_clauses { my($view) = @_; my @equality_clauses = map { "$last.$_=$view.$_" } qw(size buffersize); $last = $view; return @equality_clauses; } #### use strict; my @views = ( 'view0', 'view1', 'view2', 'view3' ); my %columns = ( 'view0' => [qw(buffersize size value)], 'view1' => [qw(buffersize size value)], 'view2' => [qw(buffersize size value)], 'view3' => [qw(buffersize size value)] ); print build_query(\@views, \%columns), "\n"; my $last; sub build_query { my($views, $columns) = @_; my $first = shift @{$views}; $last = $first; return join(' full outer join ', $first, view_on_clauses($views, $columns)); } sub view_on_clauses { my($views, $columns) = @_; return map { join(' ', $_, 'on', and_clause($_, $columns)) } @{$views}; } sub and_clause { my($view, $columns) = @_; return join(' and ', equality_clauses($view, $columns)); } sub equality_clauses { my($view, $columns) = @_; my @equality_clauses = ( ); # Convert map into explicit for loop because we need to # merge together two different arrays. If the column names # are always the same, then map can be used. for (my $i = 0; $i < @{$columns->{$view}} - 1; ++$i) { push @equality_clauses, $last . '.' . $columns->{$last}[$i] . '=' . $view . '.' . $columns->{$view}[$i]; } $last = $view; return @equality_clauses; }