1 files changed, 23 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index d942bfbe..8c76d5df 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -74,4 +74,27 @@ CREATE TABLE position(date date,
        bbg_type bbg_type,
        strategy bond_strat,
        asset_class asset_class,
+       presettle_principal float,
+       presettle_interest float,
+       settle_date date,
        PRIMARY KEY(identifier, date));
+
+CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL)
+RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat,
+              last_settle_date date, notional float) AS $$
+DECLARE sqlquery text;
+BEGIN
+  sqlquery := 'WITH temp as (SELECT bonds.identifier, bonds.description, asset_class, settle_date, folder,
+          sum(faceamount*(2*buysell::int-1))
+          OVER (PARTITION by bonds.identifier) from bonds where trade_date<=$1)
+  SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, sum AS notional
+  FROM temp
+  WHERE (sum>0 or settle_date>=$1)';
+  IF p_class is not NULL THEN
+	sqlquery := sqlquery || 'and asset_class=$2';
+  END IF;
+  sqlquery := sqlquery || 'order by identifier, settle_date';
+  RETURN QUERY EXECUTE sqlquery
+  USING p_date, p_class;
+END;
+$$ LANGUAGE plpgsql;