73 lines
2.7 KiB
PL/PgSQL
73 lines
2.7 KiB
PL/PgSQL
-- A lesson on dynamic inserts with DO
|
|
set search_path=census;
|
|
DROP TABLE IF EXISTS lu_fact_types;
|
|
CREATE TABLE lu_fact_types(fact_type_id serial
|
|
, category varchar(100)
|
|
, fact_subcats varchar(255)[], short_name varchar(50)
|
|
, CONSTRAINT pk_lu_fact_types PRIMARY KEY (fact_type_id) );
|
|
DO language plpgsql $$
|
|
DECLARE var_sql text;
|
|
BEGIN
|
|
var_sql := string_agg('INSERT INTO lu_fact_types( category,
|
|
fact_subcats, short_name )
|
|
SELECT ''Housing'', array_agg(s' || lpad(i::text,2,'0') || ') As fact_subcats
|
|
, ' || quote_literal('s' || lpad(i::text,2,'0') ) || ' As short_name
|
|
FROM staging.factfinder_import
|
|
WHERE s' || lpad(i::text,2,'0') || ' ~ ''^[a-zA-Z]+'' ', ';')
|
|
FROM generate_series(1,51) As i ;
|
|
var_sql := var_sql || ';' ||
|
|
string_agg('INSERT INTO lu_fact_types( category,
|
|
fact_subcats, short_name )
|
|
SELECT ''Population'', array_agg(d' || lpad(i::text,3,'0') || ') As fact_subcats
|
|
, ' || quote_literal('d' || lpad(i::text,3,'0') ) || ' As short_name
|
|
FROM staging.pop_import
|
|
WHERE d' || lpad(i::text,3,'0') || ' ~ ''^[a-zA-Z]+'' ', ';')
|
|
FROM generate_series(1,17) As i ;
|
|
RAISE NOTICE '%', var_sql;
|
|
EXECUTE var_sql;
|
|
END$$;
|
|
|
|
-- No longer in book Building Facts
|
|
set search_path=census;
|
|
DROP TABLE IF EXISTS facts;
|
|
CREATE TABLE IF NOT EXISTS facts(fact_type_id integer
|
|
, tract_id varchar(11)
|
|
, yr integer
|
|
,val numeric(12,3)
|
|
, perc numeric(6,2)
|
|
, CONSTRAINT pk_facts PRIMARY KEY (fact_type_id, tract_id, yr) );
|
|
DO language plpgsql $$
|
|
DECLARE var_sql text;
|
|
BEGIN
|
|
var_sql := string_agg('INSERT INTO facts(
|
|
fact_type_id, tract_id, yr, val, perc )
|
|
SELECT ' || ft.fact_type_id::text || ', geo_id2
|
|
, 2011, s' || lpad(i::text,2,'0') || '::integer As val
|
|
, CASE WHEN s' || lpad(i::text,2,'0')
|
|
|| '_perc LIKE ''(X%'' THEN NULL ELSE s' || lpad(i::text,2,'0')
|
|
|| '_perc END::numeric(5,2) As perc
|
|
FROM staging.factfinder_import AS ff
|
|
WHERE s' || lpad(i::text,2,'0') || ' ~ ''^[0-9]+'' ', ';')
|
|
FROM generate_series(1,51) As i
|
|
INNER JOIN lu_fact_types AS ft ON ( ('s' || lpad(i::text,2,'0')) = ft.short_name ) ;
|
|
|
|
var_sql := var_sql || ';' || string_agg('INSERT INTO facts(
|
|
fact_type_id, tract_id, yr, val)
|
|
SELECT ' || ft.fact_type_id::text || ', geo_id2
|
|
, 2011, d' || lpad(i::text,3,'0') || '::integer As val
|
|
FROM staging.pop_import AS ff
|
|
WHERE d' || lpad(i::text,3,'0') || ' ~ ''^[0-9]+'' ', ';')
|
|
FROM generate_series(1,17) As i
|
|
INNER JOIN lu_fact_types AS ft ON ( ('d' || lpad(i::text,3,'0')) = ft.short_name ) ;
|
|
EXECUTE var_sql;
|
|
END$$;
|
|
|
|
--builind lu_tracts --
|
|
set search_path=census;
|
|
CREATE TABLE IF NOT EXISTS lu_tracts(tract_id varchar(11), tract_long_id varchar(25)
|
|
, tract_name varchar(150)
|
|
, CONSTRAINT pk_lu_tracts PRIMARY KEY (tract_id));
|
|
INSERT INTO lu_tracts( tract_id, tract_long_id, tract_name)
|
|
SELECT geo_id2, geo_id, geo_display
|
|
FROM staging.factfinder_import
|
|
WHERE geo_id2 ~ '^[0-9]+'; |