diff options
| -rw-r--r-- | sql/dawn.sql | 51 |
1 files changed, 42 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 76652034..579a0fee 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -69,7 +69,7 @@ CREATE TABLE position(date date, unsettled_interest float, PRIMARY KEY(identifier, date)); -CREATE TABLE position_indicative(identifier varchar(12) PRIMARY KEY, +CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, cusip varchar(9), isin varchar(12), description varchar(32), @@ -79,23 +79,56 @@ CREATE TABLE position_indicative(identifier varchar(12) PRIMARY KEY, asset_class asset_class, paid_down date default 'Infinity') -CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL) +CREATE TABLE marks(date date, + identifier varchar(12), + price float, + PRIMARY KEY(identifier, date)); + +CREATE TABLE payment_history( + identifier varchar(12), + date date, + principal_paydown float, + interest float, + principal_writedown float, + recovery float, + +CREATE OR REPLACE function list_marks(p_date date) +RETURNS TABLE(identifier varchar(12), price float) AS $$ +BEGIN + RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date) 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); +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE function list_positions(p_date date, + p_class asset_class DEFAULT NULL, + include_unsettled boolean DEFAULT True) RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat, last_settle_date date, notional float, bbg_type bbg_type) AS $$ DECLARE sqlquery text; +DECLARE asset_opt text; +DECLARE unsettled_opt text; BEGIN + + IF p_class is not NULL THEN + asset_opt := 'and position_indicative.asset_class=$2 '; + ELSE + asset_opt := ''; + END IF; + IF include_unsettled THEN + unsettled_opt = 'or settle_date<=$1'; + ELSE + unsettled_opt = ''; + END IF; sqlquery := 'WITH temp as (SELECT bonds.identifier, bonds.description, asset_class, settle_date, folder, sum(faceamount*(2*buysell::int-1)) OVER (PARTITION by bonds.identifier) from bonds where trade_date<=$1) SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, sum AS notional, position_indicative.bbg_type FROM temp LEFT JOIN position_indicative USING (identifier) - WHERE (sum>0 or settle_date<=$1) and paid_down>$1 '; - IF p_class is not NULL THEN - sqlquery := sqlquery || 'and position_indicative.asset_class=$2 '; - END IF; - sqlquery := sqlquery || 'order by identifier, settle_date'; - RETURN QUERY EXECUTE sqlquery - USING p_date, p_class; + WHERE (sum>0 '||unsettled_opt||') and paid_down>$1 '||asset_opt + ||' order by identifier, settle_date'; + RETURN QUERY EXECUTE sqlquery USING p_date, p_class; END; $$ LANGUAGE plpgsql; |