diff options
| -rw-r--r-- | sql/serenitasdb.sql | 22 |
1 files changed, 14 insertions, 8 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index b4bb4be1..fcf4fff4 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -394,6 +394,7 @@ CREATE TABLE risk_numbers( GRANT ALL ON risk_numbers to serenitas_user;
+
CREATE OR REPLACE FUNCTION riskmonitor_getindicesinfo2(
IN p_date date,
IN p_fromseries smallint,
@@ -406,22 +407,27 @@ CREATE OR REPLACE FUNCTION riskmonitor_getindicesinfo2( 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';
+ tenor_cat := format('SELECT * FROM unnest(%L::tenor[])', tenorarray);
+ what_query := 'SELECT series, tenor, %I FROM index_quotes where index=%L and series>=%L and date = %L ORDER BY series, tenor';
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))
+ 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 DISTINCT ON (series) * FROM index_version
+ WHERE index_version.series>=p_fromseries AND index_version.index=p_index AND lastdate>=p_date
+ ORDER BY series, lastdate),
+ 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)
+ FROM mat JOIN pxtable USING (series) JOIN sprdtable USING (series) JOIN durtable USING (series)
JOIN indic USING (series) ORDER by mat.series;
END;
|