diff options
| -rw-r--r-- | sql/dawn.sql | 37 |
1 files changed, 27 insertions, 10 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 76757f1a..e57ea3cc 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -672,14 +672,22 @@ SERVER postgresql_server; -- update bonds set identifier='073879R75_A' where identifier='073879R75'; -- refresh materialized view factors_history; -CREATE OR REPLACE function list_subprime_data(p_date date, VARIADIC p_cusip varchar(9)[]) +CREATE OR REPLACE function list_subprime_data(p_date date, orig_flag bool, VARIADIC p_cusip varchar(9)[]) RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float, percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float, io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float) AS $$ +DECLARE + query text; + opt_constraint text; BEGIN -RETURN QUERY -WITH left_table AS ( - WITH temp AS (SELECT * from priced where date(timestamp)=p_date) +IF NOT orig_flag THEN + opt_constraint := 'AND normalization =''current_notional'''; +ELSE + opt_constraint := ''; +END IF; +query:= +'WITH left_table AS ( + WITH temp AS (SELECT * from %I where date(timestamp)=$1) SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield, c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po, c.delta_hpi FROM (SELECT * FROM temp WHERE model_version=1) a, @@ -689,11 +697,11 @@ WITH left_table AS ( right_table AS ( WITH temp AS( SELECT cusip, PV, percentile - FROM priced_percentiles - WHERE timestamp BETWEEN p_date AND p_date + INTERVAL '1 day' + FROM %I + WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day'' AND model_version=3 - AND percentile in (5, 25, 50, 75, 95) - AND normalization ='current_notional') + AND percentile in (5, 25, 50, 75, 95)' || opt_constraint + ||') SELECT a.cusip, a.PV AS pv5, b.PV AS pv25, c.PV AS pv50, d.PV AS pv75, e.PV AS pv95 FROM (SELECT cusip, PV FROM temp WHERE percentile=5) a, (SELECT cusip, PV FROM temp WHERE percentile=25) b, @@ -705,5 +713,14 @@ SELECT left_table.v1, left_table.v2, left_table.v3, left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW, left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi -FROM left_table, right_table, (SELECT unnest(p_cusip) AS cusip) l -WHERE left_table.cusip=right_table.cusip AND left_table.cusip=l.cusip; +FROM left_table, right_table, (SELECT unnest($2) AS cusip) l +WHERE left_table.cusip=right_table.cusip AND left_table.cusip=l.cusip'; +IF orig_flag THEN + query := format(query, 'priced_orig_ntl', 'priced_percentiles_orig_ntl'); +ELSE + query := format(query, 'priced', 'priced_percentiles'); +END IF; + +RETURN QUERY EXECUTE query USING p_date, p_cusip; +END +$$ LANGUAGE plpgsql; |