diff options
| -rw-r--r-- | sql/serenitasdb.sql | 7 |
1 files changed, 4 insertions, 3 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index bb6b262e..27dd71a1 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -184,7 +184,7 @@ CREATE TABLE bbg_markit_mapping( GRANT ALL ON bbg_markit_mapping to serenitas_user;
-CREATE TABLE cds_quotes(
+CREATE TABLE IF NOT EXISTS cds_quotes(
Date Date,
curve_ticker text,
UpfrontBid float,
@@ -196,6 +196,7 @@ CREATE TABLE cds_quotes( PRIMARY KEY(curve_ticker, Date));
GRANT ALL ON cds_quotes TO serenitas_user;
+CREATE INDEX IF NOT EXISTS cds_quotes_date_index ON cds_quotes(date);
CREATE TABLE markit_tranche_quotes(
quotedate date,
@@ -260,9 +261,9 @@ CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date) CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
BEGIN
- RETURN QUERY SELECT b.*
+ RETURN QUERY SELECT b.*
FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
- WHERE c.Date<=$1 GROUP BY c.curve_ticker) a
+ WHERE c.Date BETWEEN $1 - interval '1 month' AND $1 GROUP BY c.curve_ticker) a
JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
END;
-- simpler query but slower
|