diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 35 |
1 files changed, 8 insertions, 27 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 64fcf7d7..1f4b86c7 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -432,7 +432,7 @@ DECLARE query text; BEGIN - query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, + query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.folder, cds.maturity, cds.fixed_rate, cds.currency, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN -1 ELSE 1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.attach, cds.detach) AS notional FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date> $1) and cds.trade_date <=$1 %s) @@ -452,36 +452,18 @@ END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions (p_date date, strat cds_strat DEFAULT NULL::cds_strat) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat, maturity date, fixed_rate float, currency currency, notional float) AS $$ BEGIN IF strat IS NULL THEN RETURN QUERY EXECUTE query_positions('cds') USING p_date; ELSE - RETURN QUERY SELECT a.security_id, a.security_desc, a.maturity, a.fixed_rate, - a.currency, a.notional - FROM list_cds_positions_by_strat(p_date) a - WHERE folder=strat; + RETURN QUERY EXECUTE query_positions('cds') || format(' AND tmp.folder=$2') USING p_date, strat; END IF; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_positions_by_strat(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, currency currency, folder cds_strat, notional float) AS $$ -BEGIN -RETURN QUERY - WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, - cds.currency, cds.folder, - SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN -1 ELSE 1 END)) - OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder) AS notional - FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND - cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS') - SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; -END; -$$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION cds_globeop_name(redcode text, fixed_rate float, maturity date, index index_type, tenor tenor) RETURNS text AS $$ @@ -502,18 +484,17 @@ END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_positions(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, currency currency, attach smallint, detach smallint, - initial_margin_percentage float, - notional float) AS $$ +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat, + maturity date, fixed_rate float, currency currency, attach smallint, + detach smallint, initial_margin_percentage float, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_positions(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, currency currency, notional float) AS $$ +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), strategy cds_strat, + maturity date, fixed_rate float, currency currency, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('abs') USING p_date; END; |