diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 34 |
1 files changed, 34 insertions, 0 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index a209083a..ffca9741 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -377,3 +377,37 @@ CREATE TABLE risk_numbers( PRIMARY KEY(date, index, series, tenor));
GRANT ALL ON risk_numbers to serenitas_user;
+
+CREATE OR REPLACE FUNCTION riskmonitor_getindicesinfo2(
+ IN p_date date,
+ IN p_fromseries smallint,
+ IN p_index index_type,
+ IN tenorarray tenor[])
+ RETURNS TABLE(series smallint, redindexcode text, indexfactor float, t1price float, t1maturity date,
+ t1sprd float, t1dur float, t2price float, t2maturity date, t2sprd float, t2dur float,
+ t3price float, t3maturity date, t3sprd float, t3dur float) AS $$
+ DECLARE
+ tenor_cat text;
+ what_query text;
+BEGIN
+ tenor_cat := format('SELECT * FROM unnest(%L)', tenorarray);
+ what_query := 'SELECT series, tenor, %I FROM index_quotes where index=%L and series>=%L and date = %L';
+
+ RETURN QUERY
+ WITH mat AS (SELECT a.series, array_agg(a.maturity order by tenor) AS maturity from index_maturity a WHERE a.series>=p_fromseries AND a.index=p_index AND a.tenor=ANY(tenorarray) GROUP BY a.series),
+ indic AS (SELECT * FROM index_version WHERE index_version.series>=p_fromseries AND index_version.index=p_index AND lastdate='infinity'),
+ pxtable AS (SELECT * from crosstab(format(what_query, 'closeprice', p_index, p_fromseries, p_date), tenor_cat) AS ct(series smallint, tenor1 float, tenor2 float, tenor3 float)),
+ sprdtable AS (SELECT * from crosstab(format(what_query, 'closespread', p_index, p_fromseries, p_date), tenor_cat) AS ct(series smallint, tenor1 float, tenor2 float, tenor3 float)),
+ durtable AS (SELECT * from crosstab(format(what_query, 'duration', p_index, p_fromseries, p_date), tenor_cat) AS ct(series smallint, tenor1 float, tenor2 float, tenor3 float))
+
+ SELECT mat.series, indic.redindexcode, indic.indexfactor,
+ pxtable.tenor1, mat.maturity[1], sprdtable.tenor1, durtable.tenor1,
+ pxtable.tenor2, mat.maturity[2], sprdtable.tenor2, durtable.tenor2,
+ pxtable.tenor3, mat.maturity[3], sprdtable.tenor3, durtable.tenor3
+
+ FROM mat LEFT JOIN pxtable USING (series) JOIN sprdtable USING (series) JOIN durtable USING (series)
+ JOIN indic USING (series) ORDER by mat.series;
+
+END;
+ $$
+ LANGUAGE plpgsql;
|