Created
July 24, 2015 20:03
-
-
Save thrasibule/9a86a3ac06c9d7a433f3 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- -*- mode: sql; sql-product: postgres; -*- | |
CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', | |
'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', | |
'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW'); | |
CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared'); | |
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 TABLE counterparties(code varchar(12) primary key, | |
name text, | |
city text, | |
state varchar(2), | |
location text, | |
dtc_number integer, | |
sales_contact text, | |
sales_email text, | |
sales_phone text, | |
valuation_contact1 text, | |
valuation_email1 text, | |
valuation_contact2 text, | |
valuation_email2 text, | |
valuation_contact3 text, | |
valuation_email3 text, | |
valuation_contact3 text, | |
valuation_email3 text, | |
valuation_contact4 = text, | |
valuation_email4 = text, | |
notes text); | |
CREATE INDEX ON counterparties(name); | |
CREATE TABLE bonds(id serial primary key, | |
deal_id varchar(28), | |
lastupdate timestamp, | |
action action, | |
folder bond_strat, | |
custodian varchar(12), | |
cashaccount varchar(10), | |
cp_code varchar(12) references counterparties(code), | |
trade_date date, | |
settle_date date, | |
cusip varchar(9), | |
isin varchar(12), | |
description varchar(32), | |
faceamount float, | |
price float, | |
accrued float, | |
asset_class asset_class, | |
ticket text); | |
CREATE TABLE position(date date, | |
isin varchar(12), | |
cusip varchar(9), | |
identifier varchar(12), | |
description varchar(32), | |
notional float, | |
face_amount float, | |
coupon float, | |
currency currency, | |
factor float, | |
price float, | |
market_value_local float, | |
market_value_usd float, | |
accrued float, | |
days_accrued float, | |
start_accrued_date date, | |
factor_pay_date date, | |
paydown float, | |
writedown float, | |
bbg_type bbg_type, | |
strategy bond_strat, | |
asset_class asset_class, | |
presettle_principal float, | |
presettle_interest float, | |
settle_date date, | |
PRIMARY KEY(identifier, date)); | |
CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL) | |
RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat, | |
last_settle_date date, notional float) AS $$ | |
DECLARE sqlquery text; | |
BEGIN | |
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 | |
FROM temp | |
WHERE (sum>0 or settle_date>=$1)'; | |
IF p_class is not NULL THEN | |
sqlquery := sqlquery || 'and asset_class=$2'; | |
END IF; | |
sqlquery := sqlquery || 'order by identifier, settle_date'; | |
RETURN QUERY EXECUTE sqlquery | |
USING p_date, p_class; | |
END; | |
$$ LANGUAGE plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment