diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 30 |
1 files changed, 16 insertions, 14 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 87ad9604..630568d5 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -143,6 +143,7 @@ CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, accrued_payment float, current_face float, allocated boolean NOT NULL DEFAULT false, + stale bool NOT NULL DEFAULT true, CONSTRAINT bonds_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); CREATE OR REPLACE FUNCTION notify_id() @@ -150,12 +151,13 @@ RETURNS trigger AS $$ BEGIN PERFORM pg_notify(TG_TABLE_NAME, NEW.id::text); -RETURN NULL; +NEW.stale = true; +RETURN NEW; END $$ LANGUAGE plpgsql; -CREATE TRIGGER bond_notify AFTER +CREATE TRIGGER bond_notify BEFORE INSERT OR UPDATE OF identifier, cusip, @@ -413,9 +415,7 @@ CREATE TABLE futures ( lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder future_strat NOT NULL, - custodian varchar(12) NOT NULL, - cashaccount varchar(12) NOT NULL, - cp_code varchar(12) NOT NULL, + account_code varchar(12) NOT NULL REFERENCES accounts(code), trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, @@ -431,8 +431,8 @@ CREATE TABLE futures ( fund fund NOT NULL DEFAULT 'SERCGMAST'::fund, CONSTRAINT futures_dealid_key UNIQUE (dealid), CONSTRAINT futures_pkey PRIMARY KEY (id), - CONSTRAINT futures_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code) ); + ALTER table futures OWNER TO dawn_user; CREATE trigger dealid after insert on futures for each row execute procedure auto_dealid(); @@ -452,6 +452,7 @@ CREATE TABLE wires ( ALTER table wires OWNER TO dawn_user; CREATE trigger dealid after insert on wires for each row execute procedure auto_dealid(); + CREATE TABLE spots ( id serial NOT NULL, fund fund NOT NULL DEFAULT 'SERCGMAST'::fund, @@ -459,9 +460,7 @@ CREATE TABLE spots ( lastupdate timestamp NULL DEFAULT now(), "action" action NOT NULL, folder spot_strat NOT NULL, - custodian varchar(12) NOT NULL, - cashaccount varchar(12) NOT NULL, - cp_code varchar(12) NOT NULL, + account_code varchar(12) NOT NULL REFERENCES accounts(code), trade_date date NOT NULL, settle_date date NOT NULL, spot_rate float8 NOT NULL, @@ -472,7 +471,6 @@ CREATE TABLE spots ( commission_currency currency NULL, commission float8 NULL, CONSTRAINT spots_pkey PRIMARY KEY (id), - CONSTRAINT spots_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES counterparties(code) ); CREATE TABLE terminations ( @@ -791,11 +789,15 @@ WHERE date <= p_date ORDER BY identifier, date DESC) b USING (identifier) END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE VIEW bonds_trades AS -(SELECT identifier, asset_class, trade_date, settle_date, buysell, price, folder, - principal_payment, accrued_payment, notional AS faceamount, fund +CREATE VIEW bond_trades AS +(SELECT dealid, tradeid, bond_allocation.id AS id, identifier, description, asset_class, trade_date, settle_date, buysell, price, accrued, folder, + principal_payment * notional/sum(notional) OVER w AS principal_payment, accrued_payment* notional/sum(notional) OVER w AS accrued_payment , + current_face * notional/sum(notional) OVER w AS current_face , notional AS faceamount, fund, counterparties.code AS cp_code, counterparties.name AS counterparty FROM bonds LEFT JOIN bond_allocation ON bonds.id = bond_allocation.tradeid -LEFT JOIN accounts USING (code)); +LEFT JOIN accounts USING (code) +LEFT JOIN counterparties ON bonds.cp_code=counterparties.code +WINDOW w AS (PARTITION BY tradeid)) +; CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, |