diff options
| -rw-r--r-- | sql/et_tables.sql | 13 |
1 files changed, 13 insertions, 0 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 9d4e1ad4..d5f8361c 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -597,6 +597,19 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) ALTER FUNCTION et_cusip_details(varchar(10)[]) OWNER TO et_user; +CREATE OR REPLACE FUNCTION historical_cusip_risk(p_date date, p_cusips VARIADIC varchar(10)[]) + RETURNS TABLE(duration float, delta float, price float, pricingdate date) AS $$ + BEGIN + RETURN QUERY SELECT b.duration, b.delta, b.price, b.updatedate FROM + (SELECT unnest(p_cusips) AS cusip, generate_subscripts(p_cusips, 1) AS id) a + LEFT JOIN (SELECT DISTINCT ON (cusip) * FROM et_cusip_model_numbers GROUP BY cusip, updatedate + HAVING updatedate <= p_date) AS b USING (cusip) ORDER BY id; + END + $$ LANGUAGE plpgsql; + +ALTER FUNCTION historical_cusip_risk(date, varchar(10)[]) + OWNER TO et_user; + CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusips VARIADIC varchar(10)[]) -- NOT USED anymore, wasn't working very well RETURNS TABLE(p_dealname varchar(10)) AS $$ |