diff options
Diffstat (limited to 'sql/serenitasdb.sql')
| -rw-r--r-- | sql/serenitasdb.sql | 34 |
1 files changed, 16 insertions, 18 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 42ff5338..2716cba6 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -401,17 +401,14 @@ CREATE TABLE bbg_index_quotes( PRIMARY KEY(date, ticker, version));
CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series integer, pg_tenor text,
- pg_date date)
- RETURNS SETOF tranche_quotes AS
- $$
+ pg_date date) RETURNS SETOF tranche_quotes AS
+$$
DECLARE r RECORD;
- DECLARE indexname text;
DECLARE lower_attach smallint;
DECLARE flag boolean;
BEGIN
- indexname := pg_index_type || pg_series;
- IF lower(indexname) ='hy9' or lower(indexname) ='hy10'
+ IF lower(pg_index_type) ='hy' AND pg_series in (9, 10)
THEN
lower_attach = 10::smallint;
ELSE
@@ -419,29 +416,30 @@ CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series inte END IF;
flag := FALSE;
FOR r in EXECUTE
- 'SELECT DISTINCT quotesource, quotedate FROM TRANCHE_QUOTES
- WHERE index=$1::index_type AND series=$2 AND quotedate::date=$3
+ 'SELECT DISTINCT quotesource, quotedate FROM tranche_quotes
+ WHERE index=$1::index_type AND series=$2 AND date(timezone(''localtime'', quotedate))=$3
AND tenor =$4::tenor ORDER BY quotedate desc'
USING pg_index_type, pg_series, pg_date, pg_tenor LOOP
IF pg_index_type = 'HY' AND pg_series >=15 THEN
EXECUTE
- 'SELECT array_agg(attach ORDER by attach asc)||100::smallint=$6||array_agg(detach) FROM
- tranche_quotes where index=$1::index_type and series=$2 and tenor=$3::tenor
- and quotedate=$4 and detach-attach!=5::smallint and quotesource=$5'
+ 'SELECT array_agg(attach ORDER BY attach ASC)||100::smallint = $6||array_agg(detach ORDER BY detach ASC)
+ FROM tranche_quotes
+ WHERE index=$1::index_type AND series=$2 AND tenor=$3::tenor
+ AND quotedate=$4 AND detach-attach!=5::smallint AND quotesource=$5'
INTO flag
USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
IF flag THEN
RETURN QUERY
SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series= pg_series
AND tenor=pg_tenor::tenor AND quotedate=r.quotedate AND detach-attach!=5::smallint AND
- quotesource=r.quotesource ORDER BY attach asc;
- RETURN;
+ quotesource=r.quotesource ORDER BY attach ASC;
+ RETURN;
END IF;
ELSE
EXECUTE
- 'SELECT array_agg(attach ORDER BY attach asc)||100::smallint=$6||array_agg(detach) FROM
- tranche_quotes where index=$1::index_type and series=$2 and tenor=$3::tenor
- and quotedate=$4 and quotesource=$5'
+ 'SELECT array_agg(attach ORDER BY attach asc)||100::smallint = $6||array_agg(detach ORDER BY detach ASC)
+ FROM tranche_quotes
+ WHERE index=$1::index_type AND series=$2 AND tenor=$3::tenor AND quotedate=$4 AND quotesource=$5'
INTO flag
USING pg_index_type, pg_series, pg_tenor, r.quotedate, r.quotesource, lower_attach;
IF flag THEN
@@ -449,12 +447,12 @@ CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series inte SELECT * FROM tranche_quotes WHERE index=pg_index_type::index_type AND series=pg_series
AND tenor=pg_tenor::tenor AND quotedate=r.quotedate AND quotesource=r.quotesource
ORDER BY attach asc;
- RETURN;
+ RETURN;
END IF;
END IF;
END LOOP;
END;
- $$ language plpgsql;
+$$ language plpgsql;
CREATE TABLE risk_numbers(
date date,
|