1 files changed, 32 insertions, 1 deletions

diff --git a/sql/dawn.sql b/sql/dawn.sql
index 89e9091a..fa80f8cf 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -968,4 +968,35 @@ CREATE TABLE pnl_reports(
         PRIMARY KEY(date, row)
 );
 
-CREATE INDEX on pnl_reports (date);
\ No newline at end of file
+CREATE INDEX on pnl_reports (date);
+
+CREATE TYPE mark_list AS (date date,
+                      identifier text,
+                      "BROKER" float,
+                      "BVAL" float,
+                      "IDC" float,
+                      "MANAGER" float,
+                      "MARKIT" float,
+                      "PB" float,
+                      "PRICESERVE" float,
+                      "PRICINGDIRECT" float,
+                      "REUTERS" float,
+                      "S&P" float);
+
+CREATE OR REPLACE FUNCTION example() RETURNS SETOF mark_list
+AS $$
+import pandas as pd
+import numpy as np
+c = plpy.cursor("SELECT identifier, date, final AS source, mark FROM external_marks " \
+                "LEFT JOIN mark_source_mapping " \
+                "ON mark_source_mapping.globeop = external_marks.source " \
+                "ORDER BY date, identifier")
+df = pd.DataFrame.from_records(c)
+for t in (df.
+            groupby(['date', 'identifier', 'source']).
+            mean().
+            unstack(-1).
+            reset_index().
+            itertuples(index=False)):
+   yield [t[0], t[1]] + [None if np.isnan(v) else v for v in t[2:]]
+$$ LANGUAGE plpythonu;