Changes between Initial Version and Version 1 of Recursive

Timestamp:

Jun 1, 2012 4:26:17 PM (13 years ago)

Author:

cedric

Comment:

--

Recursive

@@ +1 @@
+{{{
+#!sql
+CREATE FUNCTION rht.abondance_amont(integer) RETURNS integer AS
+$fonction$
+                WITH parcours(id_drain, abondance_amont) AS
+                (
+                               WITH RECURSIVE parcours(id_drain, abondance_amont) AS
+                              (
+                                               SELECT id_drain, abondance FROM rht.crosstab_rhtvs2 WHERE id_drain = $1
+
+                                               UNION
+
+                                               SELECT crosstab_rhtvs2.id_drain, crosstab_rhtvs2.abondance FROM rht.crosstab_rhtvs2, parcours 
+                                               JOIN rht.crosstab_rhtvs2 AS t1 
+                                               ON parcours.id_drain =  t1.id_drain 
+                                               WHERE ST_Intersects(ST_Line_Interpolate_Point(t1.the_geom, 0), ST_Line_Interpolate_Point(crosstab_rhtvs2.the_geom, 1)) 
+                                               AND t1.the_geom && crosstab_rhtvs2.the_geom 
+                                               AND t1.exutoire = crosstab_rhtvs2.exutoire
+                               ) SELECT DISTINCT id_drain, abondance_amont  FROM parcours
+                ) SELECT ROUND(SUM(abondance_amont))::integer  FROM parcours;
+$fonction$
+LANGUAGE SQL STABLE;
+}}}