1 -- Obviously you'll want to change 'm_pioneer', and if you want to port this to other ILSes,
2 -- you'll want to change l_user_addr1_zip, l_user_city_state, l_user_addr1_county, etc.
5 DROP TABLE IF EXISTS m_pioneer.zips;
7 CREATE TABLE m_pioneer.zips (
14 CREATE OR REPLACE FUNCTION analyze_zips() RETURNS VOID AS $$
22 SELECT DISTINCT SUBSTRING(l_user_addr1_zip FROM 1 FOR 5)
23 FROM m_pioneer.actor_usr_legacy
24 WHERE l_user_addr1_zip <> ''
26 INSERT INTO m_pioneer.zips (zipcode, city_state, county, num)
27 SELECT zip, l_user_addr1_city_state, l_user_addr1_county, count(*)
28 FROM m_pioneer.actor_usr_legacy
29 WHERE l_user_addr1_zip=zip
37 CREATE OR REPLACE FUNCTION report_zips() RETURNS SETOF m_pioneer.zips AS $$
41 output m_pioneer.zips%ROWTYPE;
46 SELECT DISTINCT zipcode
51 WHERE num = ( SELECT MAX(num) FROM m_pioneer.zips WHERE zipcode=zip ) AND zipcode=zip
60 SELECT analyze_zips();
62 SELECT * FROM report_zips() ORDER BY num DESC;