Canadian General Election 41 PostgreSQL Database Schema

--
-- PostgreSQL database dump
--


SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--


CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;




--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--


COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';




SET search_path = public, pg_catalog;


SET default_tablespace = '';


SET default_with_oids = false;


--
-- Name: elecres; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--


CREATE TABLE elecres (
    ednum integer,
    edname character varying(255),
    psnum character varying(20),
    psname character varying(255),
    void boolean,
    nopoll boolean,
    merge character varying(100),
    rejected integer,
    electors integer,
    firstname character varying(100),
    midname character varying(100),
    lastname character varying(100),
    party character varying(100),
    incumbent boolean,
    elected boolean,
    votes integer
);




--
-- Name: combined; Type: VIEW; Schema: public; Owner: -
--


CREATE VIEW combined AS
    SELECT elecres.ednum, elecres.edname, CASE elecres.party WHEN 'Liberal'::text THEN 'Liberal/NDP'::character varying WHEN 'NDP-New Democratic Party'::text THEN 'Liberal/NDP'::character varying ELSE elecres.party END AS party, elecres.votes FROM elecres;




--
-- Name: combined_tbl; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--


CREATE TABLE combined_tbl (
    ednum integer,
    edname character varying(255),
    party character varying,
    votes bigint
);




--
-- Name: combinedtotal; Type: VIEW; Schema: public; Owner: -
--


CREATE VIEW combinedtotal AS
    SELECT s2.party, count(s2.party) AS seats FROM (SELECT s1.ednum, s1.edname, s1.party FROM (SELECT combined_tbl.ednum, combined_tbl.edname, combined_tbl.party, combined_tbl.votes, max(combined_tbl.votes) OVER (PARTITION BY combined_tbl.ednum, combined_tbl.edname) AS max FROM combined_tbl) s1 WHERE (s1.votes = s1.max)) s2 GROUP BY s2.party ORDER BY count(s2.party);




--
-- Name: separate; Type: VIEW; Schema: public; Owner: -
--


CREATE VIEW separate AS
    SELECT elecres.ednum, elecres.edname, elecres.party, elecres.votes FROM elecres;




--
-- Name: separate_tbl; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--


CREATE TABLE separate_tbl (
    ednum integer,
    edname character varying(255),
    party character varying(100),
    votes bigint
);




--
-- Name: separatetotal; Type: VIEW; Schema: public; Owner: -
--


CREATE VIEW separatetotal AS
    SELECT s2.party, count(s2.party) AS seats FROM (SELECT s1.ednum, s1.edname, s1.party FROM (SELECT seperate_tbl.ednum, separate_tbl.edname, seperate_tbl.party, seperate_tbl.votes, max(separate_tbl.votes) OVER (PARTITION BY seperate_tbl.ednum, separate_tbl.edname) AS max FROM seperate_tbl) s1 WHERE (s1.votes = s1.max)) s2 GROUP BY s2.party ORDER BY count(s2.party);




--
-- Name: enumenameparty_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--


CREATE INDEX enumenameparty_idx ON elecres USING btree (ednum, edname, party);




--
-- Name: public; Type: ACL; Schema: -; Owner: -
--


REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;




--
-- PostgreSQL database dump complete
--