Revision 57300
Added by Alessia Bardi over 4 years ago
materialised_views.sql | ||
---|---|---|
122 | 122 |
p.contactemail AS contactemail, |
123 | 123 |
dc.id AS collectedfromid, |
124 | 124 |
dc.officialname AS collectedfromname, |
125 |
cc.code || '@@@' || cc.name || '@@@' || cs.code || '@@@' || cs.name AS contracttype,
|
|
125 |
p.contracttype || '@@@' || p.contracttypename || '@@@' || p.contracttypescheme || '@@@' || p.contracttypescheme AS contracttype,
|
|
126 | 126 |
pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name AS provenanceaction, |
127 | 127 |
array_agg(DISTINCT i.pid || '###' || i.issuertype) AS pid, |
128 | 128 |
array_agg(DISTINCT s.name || '###' || sc.code || '@@@' || sc.name || '@@@' || ss.code || '@@@' || ss.name) AS subjects, |
129 | 129 |
array_agg(DISTINCT fp.path) AS fundingtree |
130 | 130 |
FROM projects p |
131 |
LEFT OUTER JOIN class cc ON (cc.code = p.contracttypeclass) |
|
132 |
LEFT OUTER JOIN scheme cs ON (cs.code = p.contracttypescheme) |
|
133 |
|
|
134 | 131 |
LEFT OUTER JOIN class pac ON (pac.code = p.provenanceactionclass) |
135 | 132 |
LEFT OUTER JOIN scheme pas ON (pas.code = p.provenanceactionscheme) |
136 | 133 |
|
... | ... | |
171 | 168 |
p.contactfax, |
172 | 169 |
p.contactphone, |
173 | 170 |
p.contactemail, |
171 |
p.contracttype, |
|
174 | 172 |
dc.id, |
175 | 173 |
dc.officialname, |
176 |
cc.code, cc.name, cs.code, cs.name, |
|
177 | 174 |
pac.code, pac.name, pas.code, pas.name |
178 | 175 |
); |
179 | 176 |
CREATE INDEX projects_mv_id_idx ON projects_mv (projectid); |
Also available in: Unified diff
#4967: change for datasources