11 lines
1.2 KiB
SQL
11 lines
1.2 KiB
SQL
-- colon before variable: for a prepared statement using named placeholders, this will be a parameter name of the form :name
|
|
|
|
CREATE SCHEMA IF NOT EXISTS :schema;
|
|
SET search_path to :schema, public;
|
|
|
|
-- create view
|
|
DROP VIEW IF EXISTS :schema.vw_join_gncy_rts_trps_stptms CASCADE;
|
|
--CREATE OR REPLACE VIEW :schema.vw_join_gncy_rts_trps_stptms AS SELECT gncy_rts_trps.agency_id, gncy_rts_trps.agency_name, gncy_rts_trps.route_id, gncy_rts_trps.route_short_name, gncy_rts_trps.service_id, gncy_rts_trps.trip_id, gncy_rts_trps.trip_short_name, gncy_rts_trps.trip_headsign, stptms.stop_id FROM :schema.vw_join_gncy_rts_trps AS gncy_rts_trps RIGHT JOIN :schema.stop_times AS stptms ON gncy_rts_trps.trip_id = stptms.trip_id ORDER BY gncy_rts_trps.agency_name, gncy_rts_trps.route_id, gncy_rts_trps.trip_id;
|
|
CREATE OR REPLACE VIEW :schema.vw_join_gncy_rts_trps_stptms AS SELECT gncy_rts_trps.agency_id, gncy_rts_trps.agency_name, stptms.stop_id FROM :schema.vw_join_gncy_rts_trps AS gncy_rts_trps JOIN :schema.stop_times AS stptms ON gncy_rts_trps.trip_id = stptms.trip_id GROUP BY stptms.stop_id, gncy_rts_trps.agency_id, gncy_rts_trps.agency_name ORDER BY gncy_rts_trps.agency_name;
|
|
--TODO JOIN or LEFT JOIN?
|