diff options
| -rw-r--r-- | sql/dawn.sql | 49 |
1 files changed, 35 insertions, 14 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index fb639ad8..56ed0047 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -5206,22 +5206,43 @@ CREATE TYPE spo_reason_code AS ENUM ('S0', 'S1', 'S2', 'S3', 'S4', 'S5', 'S6', ' CREATE TABLE spos ( - id int4 NOT NULL GENERATED ALWAYS AS IDENTITY, - dealid text NULL GENERATED ALWAYS AS ('SPO_'::text || id::text) STORED, - action action NOT NULL, - fund fund NOT NULL, + id int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE), + dealid text NULL GENERATED ALWAYS AS ('SPO_'::text || id::text) STORED, + "action" public."action" NOT NULL, + "fund" public."fund" NOT NULL, cash_account text NOT NULL, - trade_date date NOT NULL, - settle_date date NOT NULL, + trade_date date NOT NULL, + settle_date date NOT NULL, amount numeric(11, 2) NOT NULL, - currency currency NOT NULL, - cp_code varchar(10) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, - cp_account text NULL, - cp_contact text NULL, - cp_number text NULL, - reason_code spo_reason_code NOT NULL, + "currency" public."currency" NOT NULL, + cp_code varchar(10) NOT NULL, + cp_account text NULL, + cp_contact text NULL, + cp_number text NULL, + reason_code public.spo_reason_code NOT NULL, + quantity numeric(11, 2) NOT NULL, + cusip text NOT NULL, + dtc_number int8 NULL, CONSTRAINT spos_cash_account_key UNIQUE (cash_account), - CONSTRAINT spos_pkey PRIMARY KEY (id) + CONSTRAINT spos_pkey PRIMARY KEY (id), + CONSTRAINT spos_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES public.counterparties(code) ON UPDATE CASCADE ); +CREATE VIEW spo_trades AS SELECT s.*, a.name AS account_name, c.name AS counterparty_name FROM spos s LEFT JOIN accounts2 a USING (cash_account) LEFT JOIN counterparties c ON c.code=s.cp_code; + +CREATE OR REPLACE TRIGGER dtc_number BEFORE +INSERT OR UPDATE OF + cp_code +ON + spos +FOR EACH ROW WHEN (NEW.dtc_number IS NULL) +EXECUTE PROCEDURE update_dtc_number(); -CREATE VIEW spo_trades AS SELECT s.*, a.name AS account_name, c.name AS counterparty_name FROM spos s LEFT JOIN accounts2 a USING (cash_account) LEFT JOIN counterparties c ON c.code=s.cp_code;
\ No newline at end of file + +CREATE OR REPLACE FUNCTION update_dtc_number() +RETURNS TRIGGER +AS $$ +BEGIN +SELECT c.dtc_number FROM counterparties c INTO NEW.dtc_number where c.code=NEW.cp_code; +RETURN NEW; +END +$$ LANGUAGE plpgsql; |