monkeytime_function.sql

CREATE OR REPLACE FUNCTION goldenmonkey_time (date, text)
 RETURNS TABLE(dater date, monther text, traveltime interval, distance float, geom geometry) AS $$

	WITH RECURSIVE gtime AS (
		SELECT gid, dater, monther, datetimer, datetimer - (SELECT min(datetimer) FROM goldenmonkeys_sorted WHERE dater = $1 AND groupid = $2) AS timediff, 0::float AS distance, geom
			FROM goldenmonkeys_sorted WHERE dater = $1 AND groupid = $2	
		UNION ALL
		SELECT n.gid, w.dater, w.monther, w.datetimer, n.datetimer - w.datetimer AS timediff, ST_Distance(n.geom, w.geom) AS distance, n.geom
			FROM goldenmonkeys_sorted n, gtime w
				WHERE w.dater = $1 AND n.gid::integer = w.gid::integer + 1
	)
	SELECT max(dater) AS dater, max(monther) AS monther, max(timediff) AS traveltime, ST_Length(ST_MakeLine(geom)) AS length, ST_MakeLine(geom) FROM gtime;

$$ LANGUAGE SQL;