a3.rb

# city_document_view

def up
	execute <<-SQL
		CREATE MATERIALIZED VIEW city_document_view AS
   			SELECT cities.id as city_id,
   			cities.name as city_name,
   			cities.population as population,
   			cities.longitude as longitude,
   			cities.latitude as latitude,
   			regions.name as region_name,
   			regions.isocode as region_isocode,
   			countries.name as country_name,
   			countries.isocode as country_isocode,
   			string_agg(city_aliases.name, ' ') as aliases
   			FROM cities
   			JOIN regions ON cities.region_id = regions.id
			JOIN countries ON cities.country_id = countries.id
			JOIN city_aliases ON cities.id = city_aliases.city_id
			GROUP BY cities.id, regions.id, countries.id;
	SQL
end