diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 98 |
1 files changed, 52 insertions, 46 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 85ffdbce..cc56cb65 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -236,52 +236,58 @@ CREATE TABLE index_quotes( Spread float,
PRIMARY KEY(Date, Index, Series, Tenor));
-CREATE OR REPLACE FUNCTION get_tranche_quotes(index_type text, series integer, tenor text, date date)
+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
$$
- 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)
- }
+ DECLARE r RECORD;
+ DECLARE indexname text;
+ DECLARE lower_attach smallint;
+ DECLARE flag boolean;
+ BEGIN
+ indexname := pg_index_type || pg_series;
- 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';
+ IF lower(indexname) ='hy9' or lower(indexname) ='hy10'
+ THEN
+ lower_attach = 10::smallint;
+ ELSE
+ lower_attach = 0::smallint;
+ 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
+ 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'
+ 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;
+ 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'
+ 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 quotesource=r.quotesource
+ ORDER BY attach asc;
+ RETURN;
+ END IF;
+ END IF;
+ END LOOP;
+ END;
+ $$ language plpgsql;
|