diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 23 |
1 files changed, 14 insertions, 9 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 78d660b6..e1647940 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -271,9 +271,8 @@ CREATE VIEW latest_clo_universe AS JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update" ORDER by dealname asc; --- CREATE OR REPLACE FUNCTION historical_clo_universe(p_date date) --- RETURNS TABLE(dealname varchar(8), "Deal Name" text, varchar --- GRANT ALL ON latest_clo_universe TO et_user; +GRANT ALL ON latest_clo_universe TO et_user; + CREATE VIEW dealcusipmapping AS SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe; @@ -359,12 +358,6 @@ CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_s END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusip varchar(9), OUT p_dealname varchar(8)) AS $$ - BEGIN - SELECT dealname INTO p_dealname from dealcusipmapping where cusip=p_cusip; - END; - $$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) RETURNS TABLE(loanxid varchar(8), issuer text, dealname text, facility text, industry text, sp varchar(4), moodys varchar(4), amount float, maturity date, @@ -449,6 +442,18 @@ 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 dealname_from_cusip(p_cusips VARIADIC varchar(10)[]) + RETURNS TABLE(p_dealname varchar(10)) AS $$ + BEGIN + RETURN QUERY SELECT dealname FROM dealcusipmapping a + RIGHT JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) b + ON b.cusip = a.cusip ORDER BY b.id; + END; + $$ LANGUAGE plpgsql; + +ALTER FUNCTION dealname_from_cusip(varchar(10)[]) + OWNER TO et_user; + CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float, p_orig_thickness float, p_curr_thickness float) AS $$ |