diff options
| -rw-r--r-- | sql/dawn.sql | 19 |
1 files changed, 14 insertions, 5 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 20088b70..5de27fdf 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -256,15 +256,24 @@ END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions (p_date date) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float) AS $$ +BEGIN +RETURN QUERY EXECUTE query_positions('cds') USING p_date; +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, attach smallint, detach smallint, notional float) AS $$ BEGIN +RETURN QUERY EXECUTE query_positions('tranche') USING p_date; +END; +$$ LANGUAGE plpgsql; -RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.attach, cds.detach, -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.trade_date <=p_date) -SELECT DISTINCT ON (security_id, maturity, attach) * FROM tmp WHERE tmp.notional!=0; +CREATE OR REPLACE function list_abscds_positions(p_date date) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float) AS $$ +BEGIN +RETURN QUERY EXECUTE query_positions('abs') USING p_date; END; $$ LANGUAGE plpgsql; |