diff options
| -rw-r--r-- | sql/dawn.sql | 25 |
1 files changed, 25 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 423755a6..20088b70 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -230,6 +230,31 @@ BEGIN END; $$ LANGUAGE plpgsql; + +CREATE OR REPLACE function query_positions(p_type text DEFAULT NULL) +RETURNS text AS $$ +DECLARE + query text; + +BEGIN + query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, %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.trade_date <=$1 %s) +SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; + +IF p_type = 'tranche' THEN + RETURN format(query, 'cds.attach, cds.detach,', 'AND cds.attach is NOT NULL', ',tmp.attach'); +ELSIF p_type = 'cds' THEN + RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); +ELSIF p_type = 'abs' THEN + RETURN format(query, '', 'AND cds.folder=''MBSCDS''', ''); +ELSE + RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach'); +END IF; +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, attach smallint, detach smallint, notional float) AS $$ |