diff options
| -rw-r--r-- | sql/serenitasdb.sql | 15 |
1 files changed, 13 insertions, 2 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 9798688d..95e57869 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -50,9 +50,9 @@ CREATE TABLE index_maturity( );
CREATE OR REPLACE VIEW index_desc AS
- SELECT b.basketid, b.index, b.series, b.version, a.tenor, a.maturity, b.indexfactor, b.cumulativeloss, b.lastdate
+ SELECT b.*, a.tenor, a.maturity
FROM index_maturity a
- JOIN index_version b ON a.index = b.index AND a.series = b.series;
+ JOIN index_version b USING (index, series);
GRANT ALL ON index_maturity TO serenitas_user;
GRANT ALL ON index_version TO serenitas_user;
@@ -209,6 +209,17 @@ CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RE END;
$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION index_redcode(p_index index_type, p_series smallint, p_date date)
+ RETURNS varchar AS $$
+ DECLARE
+ p_redcode varchar;
+ BEGIN
+ SELECT redindexcode INTO p_redcode
+ FROM index_version WHERE index=p_index AND series=p_series AND lastdate>=p_date ORDER BY lastdate LIMIT 1;
+ RETURN p_redcode;
+ END;
+ $$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
RETURNS SETOF cds_issuers_old AS $$
DECLARE
|