13 lines
959 B
SQL
13 lines
959 B
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_rts_stps_trps_cnt CASCADE;
|
|
CREATE OR REPLACE VIEW :schema.vw_rts_stps_trps_cnt AS SELECT gncy_rts_trps.agency_name, gncy_rts_trps.agency_id, count(DISTINCT gncy_rts_trps.route_id) AS rts_cnt, stps_cnt.stps_cnt, count(DISTINCT gncy_rts_trps.trip_id) AS trps_cnt FROM :schema.vw_join_gncy_rts_trps AS gncy_rts_trps LEFT JOIN :schema.vw_stps_cnt AS stps_cnt ON gncy_rts_trps.agency_id = stps_cnt.agency_id GROUP BY gncy_rts_trps.agency_id, gncy_rts_trps.agency_name, stps_cnt.stps_cnt ORDER BY rts_cnt ASC, trps_cnt ASC, stps_cnt ASC;
|
|
|
|
-- create table
|
|
DROP TABLE IF EXISTS :schema.tbl_rts_stps_trps_cnt CASCADE;
|
|
CREATE TABLE IF NOT EXISTS :schema.tbl_rts_stps_trps_cnt AS SELECT * FROM :schema.vw_rts_stps_trps_cnt;
|