+-- This script creates new denormalized tables from a freshly dumped OLTP
+-- database, using a schema called "staging".
+
 BEGIN;
+    DROP SCHEMA staging;
+COMMIT;
+
+BEGIN;
+    ALTER SCHEMA public RENAME TO staging;
+    CREATE SCHEMA public;
+COMMIT;
+
+BEGIN;
+
+SET search_path = staging, public;
 
 CREATE OR REPLACE FUNCTION drop_my_object(a text, b text) RETURNS text AS $$
 BEGIN

	oaci.code_description AS outbreak_associated_code,	-- code_description?
	events.outbreak_name,

	events.event_status,					-- Change this from a code to a text value?
	inv.first_name || ' ' || inv.last_name AS investigator,
	events.event_queue_id,
	events.acuity,

END;
$$
LANGUAGE PLPGSQL VOLATILE;
COMMIT;

BEGIN;
SELECT
    drop_my_object(tablename, 'TABLE')
FROM
    pg_tables t
WHERE
    schemaname = 'staging' AND
    tablename IN (
        'dw_morbidity_events',
        'dw_contact_events',
        'dw_secondary_jurisdictions',
        'dw_place_events',
        'dw_patients',
        'dw_events_hospitals',
        'dw_addresses',				-- Consider altering the table instead of creating a new one
        'dw_email_addresses',			-- Consider altering the table instead of creating a new one
        'dw_hospitals_participations',
        'dw_lab_results',
        'dw_participations_treatments',
        'dw_events_treatments',
        'dw_patients_races',
        'dw_events_clinicians',
        'dw_events_diagnostic_facilities',
        'dw_events_reporting_agencies',
        'dw_events_reporters',
        'dw_people_races',
        'dw_place_events',
        'dw_encounters',
        'dw_encounters_labs',
        'dw_encounters_treatments'
    );
COMMIT;

BEGIN;
SELECT
    drop_my_object(relname, 'SEQUENCE')
FROM
    pg_class c
    INNER JOIN pg_namespace n
        ON (n.oid = c.relnamespace)
WHERE
    relkind = 'S' AND
    relname IN (
        'dw_patients_races_seq'
    ) AND
    n.nspname = 'public' ;
COMMIT;

BEGIN;
CREATE TABLE dw_patients AS			-- "patient" may well be the wrong name for it
SELECT
	people.id,
