1 files changed, 27 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 190b819c..5742bce2 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -8,7 +8,8 @@ CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures',
 CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL');
 CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN');
 CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp');
-CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365')
+CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365');
+CREATE type index_type AS ENUM('IG', 'HY', 'EU');
 
 CREATE TABLE counterparties(code varchar(12) primary key,
        name text,
@@ -87,6 +88,17 @@ CREATE TABLE cashflow_history(
        coupon float,
        PRIMARY KEY (identifier, date));
 
+CREATE TABLE risk_numbers(
+       identifier varchar(12) REFERENCES securities,
+       date date,
+       delta float,
+       index_delta index_type,
+       duration float,
+       wal float,
+       undiscounted_price float,
+       model_price float,
+       PRIMARY KEY (identifier, date));
+
 CREATE TABLE fx(date date PRIMARY KEY,
        eurusd float,
        cadusd float);
@@ -100,6 +112,20 @@ WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier);
 END;
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False)
+RETURNS TABLE(identifier varchar(12), price float, delta float, index_delta index_type, duration float, wal float,
+undiscounted_price float, model_price float) AS $$
+BEGIN
+  RETURN QUERY SELECT a.identifier, b.price, c.delta, c.index_delta,
+  c.duration, c.wal, c_undiscounted_price, c.model_price
+  FROM list_positions(p_date, assetclass, include_unsettled) a
+  LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks
+WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier)
+  LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_number
+  WHERE date<=p_date ORDER BY identifier, date DESC) c USING (identifier);
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE OR REPLACE function list_positions(p_date date,
        p_class asset_class DEFAULT NULL,
        include_unsettled boolean DEFAULT True)