-- create an elevation profile as an array with fixed distances between points
CREATE OR REPLACE FUNCTION elevation_profile(geom geometry, num_points integer) RETURNS integer[] AS $$
DECLARE
    -- number of points in linestring
    profile integer[] DEFAULT '{}';
BEGIN
    FOR i in 0..(num_points - 1) LOOP
        profile := array_append(profile, round(ST_Z(ST_LineInterpolatePoint(geom, i::double precision / (num_points - 1))))::integer);
    END LOOP;

    RETURN profile;
END;
$$ LANGUAGE plpgsql;

-- calculate cumulative elevation gain/loss
CREATE OR REPLACE FUNCTION cumulative_elevation(geom geometry, cent text) RETURNS integer AS $$
DECLARE
    -- number of points in linestring
    num_points integer;

    -- previous and current points
    prev geometry;
    cur geometry;

    -- keeping track of cumulative ascent and descent
    cumulative_ascent double precision DEFAULT 0;
    cumulative_descent double precision DEFAULT 0;

    -- used as a local variable
    delta_z double precision;
BEGIN
    SELECT ST_NumPoints(geom) INTO num_points;

    -- start from 2 so we can always get the previous point
    FOR i in 2..num_points LOOP
        SELECT ST_PointN(geom,i-1) INTO prev;
        SELECT ST_PointN(geom,i) INTO cur;

        delta_z := ST_Z(cur) - ST_Z(prev);

        IF delta_z < 0 THEN
            cumulative_ascent := cumulative_ascent + abs(delta_z);
        ELSE
            cumulative_descent := cumulative_descent + abs(delta_z);
        END IF;

    END LOOP;

    -- return cumulative cent
    IF cent = 'ascent' THEN
        RETURN CAST(cumulative_ascent AS integer);
    ELSIF cent = 'descent' THEN
        RETURN CAST(cumulative_descent AS integer);
    ELSE
        RETURN null;
    END IF;
END;
$$ LANGUAGE plpgsql;