diff options
| -rw-r--r-- | sql/dawn.sql | 16 |
1 files changed, 10 insertions, 6 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 6fb969fe..cdfb3252 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -605,11 +605,15 @@ CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, ALTER TABLE securities OWNER TO dawn_user; -CREATE TABLE marks(date date, - identifier varchar(12) REFERENCES securities(figi) ON DELETE CASCADE ON UPDATE CASCADE, - price float, - PRIMARY KEY(identifier, date)); +CREATE TABLE marks( + date date NOT NULL, + price float NOT NULL, + identifier varchar(12) NOT NULL REFERENCES securities(figi) ON DELETE CASCADE ON UPDATE CASCADE, + id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + UNIQUE (date, identifier) +); +CREATE UNIQUE INDEX ON marks(date, identifier); CREATE TABLE external_marks_deriv(date date NOT NULL, identifier text NOT NULL, @@ -835,9 +839,9 @@ BEGIN c.duration, c.wal, c.undiscounted_price, c.model_price FROM list_positions(p_date, assetclass, include_unsettled, p_fund) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks -WHERE date <= p_date ORDER BY identifier, date DESC) b USING (identifier) +WHERE date <= p_date ORDER BY identifier, date DESC) b ON a.figi=b.identifier LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers - WHERE date <=p_date ORDER BY identifier, date DESC) c USING (identifier); + WHERE date <=p_date ORDER BY identifier, date DESC) c USING ON a.figi=c.identifier; END; $$ LANGUAGE plpgsql; |