diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 19 |
1 files changed, 10 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index d2905dd8..20fc8921 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1274,8 +1274,9 @@ END IF; END; $$ LANGUAGE plpgsql; -CREATE TYPE LIST_CDS AS( +CREATE TYPE "LIST_CDS" AS( id integer, + fund fund, trade_date date, security_id varchar(12), security_desc varchar(32), @@ -1295,11 +1296,11 @@ CREATE TYPE LIST_CDS AS( cp_code varchar(12) ); -CREATE OR REPLACE function list_cds(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) -RETURNS SETOF LIST_CDS AS $$ +CREATE OR REPLACE function list_cds(p_date date, VARIADIC p_fund fund[] DEFAULT '{SERCGMAST}'::fund[]) +RETURNS SETOF "LIST_CDS" AS $$ BEGIN RETURN QUERY -SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, +SELECT id, fund, trade_date, security_id, security_desc, maturity, fixed_rate, currency, folder, protection, (notional - coalesce(terminated_amount, 0.)) * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END) AS notional, @@ -1309,17 +1310,17 @@ SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currenc FROM cds LEFT JOIN ( SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid) -WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount +WHERE fund=ANY(p_fund) and notional IS DISTINCT FROM terminated_amount AND trade_date <= p_date AND maturity > p_date; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds2(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) +CREATE OR REPLACE function list_cds2(p_date date, VARIADIC p_fund fund[] DEFAULT '{SERCGMAST}'::fund[]) -- Do not include unsettled terminations -RETURNS SETOF LIST_CDS AS $$ +RETURNS SETOF "LIST_CDS" AS $$ BEGIN RETURN QUERY -SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currency, +SELECT id, fund, trade_date, security_id, security_desc, maturity, fixed_rate, currency, folder, protection, (notional - coalesce(terminated_amount, 0.)) * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END) AS notional, @@ -1329,7 +1330,7 @@ SELECT id, trade_date, security_id, security_desc, maturity, fixed_rate, currenc FROM cds LEFT JOIN ( SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE fee_payment_date < p_date GROUP BY dealid) b USING (dealid) -WHERE fund=p_fund and notional IS DISTINCT FROM terminated_amount +WHERE fund=ANY(p_fund) and notional IS DISTINCT FROM terminated_amount AND trade_date <= p_date AND maturity > p_date; END; $$ LANGUAGE plpgsql; |