diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 53 |
1 files changed, 25 insertions, 28 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index d5ad3ea2..607d1b63 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -85,6 +85,7 @@ CREATE TABLE accounts2 ( "fund" fund NOT NULL, "account_type" account_type NOT NULL, active bool NOT NULL, + cp_code varchar(10), CONSTRAINT accounts2_cash_account_key UNIQUE (cash_account), CONSTRAINT accounts2_pkey PRIMARY KEY (id) ); @@ -561,7 +562,7 @@ CREATE TABLE wires ( ALTER table wires OWNER TO dawn_user; -CREATE TRIGGER dealid AFTER +CREATE TRIGGER dealid BEFORE INSERT ON wires FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); @@ -674,35 +675,31 @@ create CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ DECLARE stub text; - sqlstr text; BEGIN - sqlstr:= format('UPDATE '|| TG_TABLE_SCHEMA ||'.' ||TG_TABLE_NAME || ' SET %s WHERE id = %L AND dealid is NULL'; - - IF (TG_TABLE_NAME = 'bonds') THEN - stub := 'SC_'; - sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, -identifier = COALESCE(identifier, cusip, isin)', NEW.id); - ELSE - CASE TG_TABLE_NAME - WHEN 'cds' THEN - stub := 'SCCDS'; - WHEN 'swaptions' THEN - stub := 'SWPTN'; - WHEN 'futures' THEN - stub := 'SCFUT'; - WHEN 'wires' THEN - stub := 'SCCSH'; - WHEN 'capfloors' THEN - stub := 'CAP'; - WHEN 'spots' THEN - stub := 'SCFX'; - END CASE; - sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); - END IF; - EXECUTE sqlstr USING stub; - RETURN NEW; + IF NEW.dealid is NULL THEN + IF (TG_TABLE_NAME = 'bonds') THEN + NEW.dealid := 'SC_' || upper(left(NEW.asset_class::text,3)) || NEW.id; + ELSE + CASE TG_TABLE_NAME + WHEN 'cds' THEN + stub := 'SCCDS'; + WHEN 'swaptions' THEN + stub := 'SWPTN'; + WHEN 'futures' THEN + stub := 'SCFUT'; + WHEN 'wires' THEN + stub := 'SCCSH'; + WHEN 'capfloors' THEN + stub := 'CAP'; + WHEN 'spots' THEN + stub := 'SCFX'; + END CASE; + NEW.dealid := stub || NEW.id; + END IF; + END IF; + RETURN NEW; END; - $$ language plpgsql; +$$ language plpgsql; CREATE TRIGGER dealid AFTER INSERT ON capfloors FOR EACH ROW EXECUTE PROCEDURE auto_dealid() ; |