diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 65 |
1 files changed, 57 insertions, 8 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 185e028f..fa9d5f8b 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -214,6 +214,43 @@ CREATE TABLE swaptions(id serial PRIMARY KEY, termination_amount float, termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE); +CREATE TABLE capfloors ( + id serial PRIMARY KEY, + dealid varchar(28), + lastupdate timestamp NULL DEFAULT now(), + "action" action, + folder swaption_strat NOT NULL, + custodian varchar(12) NOT NULL, + cashaccount varchar(10) NOT NULL, + cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, + "comments" varchar(100), + floating_rate_index varchar(12) NOT NULL, + floating_rate_index_desc varchar(32), + buysell bool NOT NULL, + cap_or_floor cap_or_floor NOT NULL, + strike float8 NOT NULL, + value_date date NOT NULL, + expiration_date date NOT NULL, + premium_percent float8 NOT NULL, + pricing_type pricing_type NOT NULL, + payment_frequency frequency NOT NULL, + fixing_frequency frequency NOT NULL, + day_count_counvention day_count NULL, + bdc_convention bus_day_convention NULL, + payment_mode payment_mode NOT NULL, + payment_at_beginning_or_end begin_or_end NOT NULL, + initial_margin_percentage float8 NULL, + initial_margin_currency currency NULL, + amount float8 NOT NULL, + trade_date date NOT NULL, + swap_type capfloor_type NOT NULL, + reset_lag int4, + trade_confirm varchar, + termination_date date, + termination_amount float8, + termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE, + cpty_id text, +) CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ @@ -226,14 +263,21 @@ CREATE OR REPLACE FUNCTION auto_dealid() stub := 'SC_'; sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin)', NEW.id); - ELSIF (TG_TABLE_NAME = 'cds') THEN - stub := 'SCCDS'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'repo') THEN - stub := 'SC_REP'; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - ELSIF (TG_TABLE_NAME = 'swaptions') THEN - stub := 'SWPTN'; + ELSE + CASE TG_TABLE_NAME + WHEN 'cds' THEN + stub := 'SCCDS'; + WHEN 'repo' THEN + stub := 'SC_REP'; + WHEN 'swaptions' THEN + stub := 'SWPTN'; + WHEN 'futures' THEN + stub := 'SCFUT'; + WHEN 'wires' THEN + stub := 'SCCSH'; + WHEN 'capfloors' THEN + stub := 'CAP'; + END CASE; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; EXECUTE sqlstr USING stub; @@ -241,6 +285,11 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id); END; $$ language plpgsql; +create + trigger dealid after insert + on + capfloors for each row execute procedure auto_dealid() ; + CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); |