· 6 years ago · Apr 14, 2019, 10:12 PM
1---------------------------------------------------------
2-- Boilerplate mocking out what we already have today. --
3-- Just scroll down to the bottom. --
4---------------------------------------------------------
5
6-- Cleanup
7drop table if exists
8 import_log,
9 my_data_source,
10 meta_record,
11 my_campaign;
12drop function if exists
13 insert_meta_record (text)
14 ;
15
16-- Our "data source" table
17create table my_data_source (
18 cust_id bigint,
19 name text,
20 dob date,
21 market text,
22 nps int
23);
24
25-- Some test data
26insert into my_data_source (cust_id, name, dob, market, nps) values
27 (123, 'Adam', '11/22/93'::date, 'Minnesota', 8),
28 (456, 'Benny', '4/5/86'::date, 'Wisconsin', 3),
29 (789, 'Chris', '7/18/89'::date, 'Minnesota', 6),
30 (1011, 'Dave', '3/23/91'::date, 'Minnesota', 1),
31 (1213, 'Eric', '1/1/81'::date, 'Illinois', 7)
32;
33
34create table meta_record (
35 id bigserial primary key,
36 campaign_name text,
37 created_dt timestamp,
38 modified_dt timestamp,
39 assignee text,
40 priority int default 0
41);
42
43-- Simplified version of existing stored procedure to create meta-records for each campaign record
44create function insert_meta_record (
45 campaign_name_ text
46)
47 returns bigint
48 language plpgsql
49 as $$
50declare
51 now_ timestamp = now();
52 id_ bigint;
53begin
54 insert into meta_record (campaign_name, created_dt, modified_dt)
55 values (campaign_name_, now_, now_)
56 returning id into id_;
57
58 return id_;
59end;
60$$;
61
62create table my_campaign (
63 meta_record_id bigint references meta_record(id),
64 cust_id bigint,
65 name text,
66 dob date,
67 nps int
68);
69
70--------------------------------------
71-- Now for the interesting stuff! --
72--------------------------------------
73
74-- New table to store
75create table import_log (
76 campaign_name text,
77 process_time timestamp default now(),
78 actions jsonb,
79 row jsonb
80);
81
82do $$ -- This is a raw PL/pgSQL block = "Procedural Language: Postgres SQL".
83declare -- DECLARE blocks both declare variables and define the scope where they exist, like Python's "with" statement
84 r my_data_source;
85 inserted_id bigint;
86 actions jsonb;
87 skip_record boolean;
88begin
89
90 -- Loop over every record in the data source.
91 -- We don't use a single INSERT/SELECT statement because we need to log things as a side-effect.
92 for r in select * from my_data_source
93 loop
94 skip_record = false;
95 actions = '[]'::jsonb; -- Log of all the rules we process
96
97 -- Rules which cause us to skip a record
98 if r.market != 'Minnesota' then
99 skip_record = true;
100 actions = jsonb_insert(actions, '{-1}', '{"skip": true}', true); -- append action to the rules log
101 -- We might also want to record which rule was being processed
102 end if;
103
104 if not(skip_record) then
105
106 insert into my_campaign (
107 meta_record_id,
108 cust_id, name, dob, nps
109 ) values (
110 insert_meta_record('my_campaign'),
111 r.cust_id, r.name, r.dob, r.nps
112 )
113 returning meta_record_id into inserted_id;
114
115 -- Process additional rules in order
116 if r.nps <= 6 then
117 update meta_record
118 set assignee = 'Jakub'
119 where id = inserted_id;
120
121 actions = jsonb_insert(actions, '{-1}', jsonb_build_object('assignee', 'Jakub'), true);
122 end if;
123
124 if r.nps <= 3 then
125 update meta_record
126 set priority = 9
127 where id = inserted_id;
128
129 actions = jsonb_insert(actions, '{-1}', jsonb_build_object('priority', 9), true);
130 end if;
131
132 end if;
133
134 insert into import_log (campaign_name, process_time, actions, row)
135 values ('my_campaign', now(), actions, row_to_json(r.*));
136 end loop;
137end;
138$$;
139
140-- These are the records that got inserted:
141select * from meta_record full join my_campaign on my_campaign.meta_record_id = meta_record.id;
142 -- id campaign_name created_dt modified_dt assignee priority cid name dob nps
143 -- 1 my_campaign 2017-09-13 15:52:00.123611 2017-09-13 15:52:00.123611 0 123 Adam 1993-11-22 8
144 -- 2 my_campaign 2017-09-13 15:52:00.123611 2017-09-13 15:52:00.123611 Jakub 0 789 Chris 1989-07-18 6
145 -- 3 my_campaign 2017-09-13 15:52:00.123611 2017-09-13 15:52:00.123611 Jakub 9 1011 Dave 1991-03-23 1
146
147-- And we also have a log!
148select * from import_log;
149 -- campgn_name process_time actions row
150 -- my_campaign 2017-09-13 15:52:00.123611 [] {"dob": "1993-11-22", "nps": 8, "name": "Adam", "market": "Minnesota", "cust_id": 123}
151 -- my_campaign 2017-09-13 15:52:00.123611 [{"skip": true}] {"dob": "1986-04-05", "nps": 3, "name": "Benny", "market": "Wisconsin", "cust_id": 456}
152 -- my_campaign 2017-09-13 15:52:00.123611 [{"assignee": "Jakub"}] {"dob": "1989-07-18", "nps": 6, "name": "Chris", "market": "Minnesota", "cust_id": 789}
153 -- my_campaign 2017-09-13 15:52:00.123611 [{"assignee": "Jakub"}, {"priority": 9}] {"dob": "1991-03-23", "nps": 1, "name": "Dave", "market": "Minnesota", "cust_id": 1011}
154 -- my_campaign 2017-09-13 15:52:00.123611 [{"skip": true}] {"dob": "1981-01-01", "nps": 7, "name": "Eric", "market": "Illinois", "cust_id": 1213}
155
156-- For some reason every record has the same log time. Not sure why that is...