diff options
| -rw-r--r-- | sql/dawn.sql | 24 |
1 files changed, 24 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index faa80dc2..e8312495 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -128,6 +128,7 @@ CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, END ||id::text) STORED, folder bond_strat NOT NULL, + portfolio portfolio NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, @@ -158,6 +159,27 @@ RETURN NEW; END $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION update_portf() +RETURNS TRIGGER +AS $$ +BEGIN +NEW.portfolio = ( +CASE WHEN NEW.folder::text LIKE '%MTG%' THEN 'MORTGAGES' +WHEN NEW.folder::TEXT LIKE '%CLO%' THEN 'CLO' +WHEN NEW.folder::TEXT LIKE '%CRT%' THEN 'MORTGAGES' +WHEN NEW.folder::TEXT LIKE '%STR%' THEN 'STRUCTURED' END)::portfolio; +RETURN NEW; +END +$$ LANGUAGE plpgsql; + + +CREATE TRIGGER portf BEFORE +INSERT OR UPDATE OF + folder +ON + bonds +FOR EACH ROW +EXECUTE PROCEDURE update_portf(); CREATE TRIGGER bond_notify BEFORE INSERT OR UPDATE OF @@ -808,6 +830,7 @@ AS SELECT bonds.dealid, bonds.price, bonds.accrued, bonds.folder, + bond.portfolio, bonds.principal_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS principal_payment, bonds.accrued_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS accrued_payment, (bonds.principal_payment + bonds.accrued_payment) * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS net_amount, @@ -1716,6 +1739,7 @@ IMPORT FOREIGN SCHEMA public LIMIT TO index_version, index_version_markit, index_maturity, + index_maturity_markit, index_quotes, markit_tranche_quotes, risk_num_per_quote, |