diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 8bd43837..85ffdbce 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -235,3 +235,53 @@ CREATE TABLE index_quotes( Price float,
Spread float,
PRIMARY KEY(Date, Index, Series, Tenor));
+
+CREATE OR REPLACE FUNCTION get_tranche_quotes(index_type text, series integer, tenor text, date date)
+ RETURNS SETOF tranche_quotes AS
+ $$
+ indexname <- paste0(index_type, series)
+ sqlstr <- paste("select * from tranche_quotes",
+ "where index=$1::index_type and series=$2 and quotedate::date=$3",
+ "and tenor = $4::tenor order by attach asc")
+ query <- pg.spi.prepare(sqlstr, c(25, 21, 1082, 25))
+ r <- pg.spi.execp(query, list(index_type, series, date, tenor))
+ if(tolower(indexname) %in% c("hy9", "hy10")){
+ lower.attach <- 10
+ }else{
+ lower.attach <- 0
+ }
+ if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(lower.attach, r$detach))){
+ return(r)
+ }
+
+ sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes",
+ "where index=$1::index_type and series=$2 and quotedate::date=$3",
+ "and tenor = $4::tenor order by quotedate desc")
+ distinct.quotes <- pg.spi.execp(query, list(index_type, series, date, tenor))
+ flag <- FALSE
+ if(index_type == 'HY' && series>=15){
+ sqlstr <- paste("select * from tranche_quotes where index=$1::index_type",
+ "and series=$2 and tenor = $3::tenor and quotedate=$4",
+ "and detach-attach!=5 and quotesource=$5 order by attach asc")
+ query <- pg.spi.prepare(sqlstr, c(25, 21, 25, 1114, 25))
+ }else{
+ sqlstr <- paste("select * from tranche_quotes where index=$1::index_type",
+ "and series=$2 and tenor = $3::tenor and quotedate=$4",
+ "and quotesource=$5 order by attach asc")
+ query <- pg.spi.prepare(sqlstr, c(25, 21, 25, 1114, 25))
+ }
+ for(i in 1:nrow(distinct.quotes)){
+ r <- pg.spi.execp(query,
+ list(index_type, series, tenor,
+ distinct.quotes$quotedate[i], distinct.quotes$quotesource[i]))
+ if(all(c(r$attach, 100)==c(lower.attach, r$detach))){
+ flag <- TRUE
+ break
+ }
+ }
+ if(!flag){
+ pg.thrownotice("no quotes for that day")
+ return( 0 )
+ }
+ return( r )
+ $$ language 'plr';
|