dive4elements/river: backend/doc/schema/postgresql.sql comparison

Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)

author	Tom Gottfried <tom.gottfried@intevation.de>
date	Thu, 23 May 2013 17:32:20 +0200
parents	d12f920bbf00
children	6cdb3ee687a9

comparison

equal deleted inserted replaced

-:c97d003fd127
+:176664f84d86
 UNIQUE (wst_column_id, wst_q_range_id)
 );
 CREATE VIEW wst_value_table AS
-SELECT wcv.position AS position,
+SELECT
-w,
+wcv.position AS position,
-(SELECT q
+w,
-FROM   wst_column_q_ranges wcqr
+q,
-JOIN wst_q_ranges wqr
+wc.position AS column_pos,
-ON wcqr.wst_q_range_id = wqr.id
+w.id AS wst_id
-JOIN ranges r
+FROM wsts w
-ON r.id = wqr.range_id
+JOIN wst_columns wc
-WHERE  wcqr.wst_column_id = wc.id
+ON wc.wst_id=w.id
-AND wcv.position BETWEEN r.a AND r.b) AS q,
+JOIN wst_column_q_ranges wcqr
-wc.position                                   AS column_pos,
+ON wcqr.wst_column_id=wc.id
-w.id                                          AS wst_id
+JOIN wst_q_ranges wqr
-FROM   wst_column_values wcv
+ON wcqr.wst_q_range_id=wqr.id
-JOIN wst_columns wc
+JOIN ranges r
-ON wcv.wst_column_id = wc.id
+ON wqr.range_id=r.id
-JOIN wsts w
+JOIN wst_column_values wcv
-ON wc.wst_id = w.id
+ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b;
 ORDER  BY wcv.position ASC,
 wc.position DESC;
 -- view to select the w values of a WST
 CREATE VIEW wst_w_values AS
 SELECT wcv."position" AS km,
 wcv.w          AS w,

Mercurial > dive4elements > river