Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql.sql @ 476:77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
flys-backend/trunk@1723 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Mon, 18 Apr 2011 15:52:53 +0000 |
| parents | 3570e4af8cb2 |
| children | b35c5dc0f8b7 |
comparison
equal
deleted
inserted
replaced
| 475:9aa0eddc5221 | 476:77f337650211 |
|---|---|
| 192 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), | 192 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), |
| 193 | 193 |
| 194 UNIQUE (wst_column_id, wst_q_range_id) | 194 UNIQUE (wst_column_id, wst_q_range_id) |
| 195 ); | 195 ); |
| 196 | 196 |
| 197 CREATE VIEW wst_value_table AS | |
| 198 SELECT wcv.position AS position, | |
| 199 w, | |
| 200 (SELECT q | |
| 201 FROM wst_column_q_ranges wcqr | |
| 202 JOIN wst_q_ranges wqr | |
| 203 ON wcqr.wst_q_range_id = wqr.id | |
| 204 JOIN ranges r | |
| 205 ON r.id = wqr.range_id | |
| 206 WHERE wcqr.wst_column_id = wc.id | |
| 207 AND wcv.position BETWEEN r.a AND r.b) AS q, | |
| 208 wc.position AS column_pos, | |
| 209 w.id AS wst_id | |
| 210 FROM wst_column_values wcv | |
| 211 JOIN wst_columns wc | |
| 212 ON wcv.wst_column_id = wc.id | |
| 213 JOIN wsts w | |
| 214 ON wc.wst_id = w.id | |
| 215 ORDER BY wcv.position ASC, | |
| 216 wc.position DESC; | |
| 217 | |
| 197 COMMIT; | 218 COMMIT; |