1 files changed, 25 insertions, 31 deletions

diff --git a/sql/dawn.sql b/sql/dawn.sql
index cb1d4b9c..3a8c0e4d 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -80,19 +80,10 @@ CREATE TABLE bonds(id serial primary key,
        accrued_payment float,
        CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL));
 
-CREATE OR REPLACE FUNCTION mydealid()
-    RETURNS TRIGGER AS $$
-    BEGIN
-       UPDATE bonds SET dealid = 'SC_' ||upper(left(asset_class::text,3))||id,
-    identifier = COALESCE(identifier, cusip, isin) WHERE bonds.id = NEW.id;
-       RETURN NEW;
-    END;
-    $$ language plpgsql;
-
 CREATE TRIGGER dealid
        AFTER INSERT ON bonds
        FOR EACH ROW
-       EXECUTE PROCEDURE mydealid();
+       EXECUTE PROCEDURE auto_dealid();
 
 
 CREATE TABLE cds(id serial primary key,
@@ -127,16 +118,8 @@ CREATE TABLE cds(id serial primary key,
 
 ALTER TABLE cds OWNER TO dawn_user;
 
-CREATE OR REPLACE FUNCTION auto_cds_dealid()
-    RETURNS TRIGGER AS $$
-    BEGIN
-       UPDATE cds SET dealid = 'SCCDS' ||id WHERE cds.id = NEW.id AND dealid is Null;
-       RETURN NEW;
-    END;
-    $$ language plpgsql;
-
 CREATE TRIGGER cds_dealid AFTER INSERT ON cds
-FOR EACH ROW EXECUTE PROCEDURE auto_cds_dealid();
+FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
 
 CREATE TABLE repo(id serial primary key,
        dealid varchar(28),
@@ -168,16 +151,8 @@ CREATE TABLE repo(id serial primary key,
        CHECK (cusip is NOT NULL OR isin is NOT NULL)
 );
 
-CREATE OR REPLACE FUNCTION auto_repo_dealid()
-    RETURNS TRIGGER AS $$
-    BEGIN
-       UPDATE repo SET dealid = 'SC_REP' ||id WHERE repo.id = NEW.id AND dealid is Null;
-       RETURN NEW;
-    END;
-    $$ language plpgsql;
-
 CREATE TRIGGER repo_dealid AFTER INSERT ON repo
-FOR EACH ROW EXECUTE PROCEDURE auto_repo_dealid();
+FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
 
 ALTER TABLE repo OWNER TO dawn_user;
 
@@ -203,16 +178,35 @@ CREATE TABLE swaptions(id serial PRIMARY KEY,
        currency currency NOT NULL,
        fixed_rate float NOT NULL);
 
-CREATE OR REPLACE FUNCTION auto_swaption_dealid()
+
+CREATE OR REPLACE FUNCTION auto_dealid()
     RETURNS TRIGGER AS $$
+    DECLARE stub text;
+            sqlstr text;
     BEGIN
-       UPDATE swaptions SET dealid = 'SWPTN' ||id WHERE swaptions.id = NEW.id AND dealid is Null;
+      sqlstr:= 'UPDATE '|| 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);
+       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';
+          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
+       END IF;
+       EXECUTE sqlstr USING stub;
        RETURN NEW;
     END;
     $$ language plpgsql;
 
 CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions
-FOR EACH ROW EXECUTE PROCEDURE auto_swaption_dealid();
+FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
 
 ALTER TABLE swaptions OWNER TO dawn_user;