diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 18 |
1 files changed, 12 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 096ea24e..55a6446e 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -144,13 +144,19 @@ CREATE TABLE bond_old(id serial primary key, accrued_payment float, CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); + +CREATE OR REPLACE FUNCTION bond_dealid(int4, asset_class) RETURNS text IMMUTABLE STRICT +AS $$ +SELECT ('SC_'||CASE $2 WHEN 'Subprime' THEN 'SUB' + WHEN 'CLO' THEN 'CLO' + WHEN 'CSO' THEN 'CSO' + WHEN 'CRT' THEN 'CRT' + WHEN 'TBILL' THEN 'TB' + END||$1::text) AS result; +$$ LANGUAGE SQL; + CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - dealid varchar(28) GENERATED ALWAYS AS ('SC_'||CASE WHEN asset_class ='Subprime' THEN 'SUB' - WHEN asset_class='CLO' THEN 'CLO' - WHEN asset_class='CSO' THEN 'CSO' - WHEN asset_class='CRT' THEN 'CRT' - END - ||id::text) STORED, + dealid varchar(28) GENERATED ALWAYS AS bond_dealid(id, asset_class) STORED, folder bond_strat, portfolio portfolio NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, |