[vox-tech] postgres: run function on each row returned from select statement

[Dylan Beaudette]

On 4/23/07, Harold Lee <harold at hotelling.net> wrote:
> Dylan Beaudette wrote:
> > Is there any way to run a function, which expects a single value as an
> > argument, for each row returned from a select statement? Note that this is a
> > special function, used in the following way:
> >
> > SELECT sum(length(the_geom )) as dist_meters
> > FROM
> > shortest_path_as_geometry('roads', 2192, 154) ;
> >
> > In the above example the function "shortest_path_as_geometry" expects three
> > arguments... Is there any way to feed a function like this its arguments from
> > column returned from a previous select statement? Or, would this function
> > have to be re-written to allow for this flexibility?
> >
> > thanks in advance,
> >
> >
>
> You question isn't 100% clear to me, but I'm not going to let that stop
> me from opining :-)
>
> Sounds like you have some query that returns (string, roadid1, roadid2)
> and you want to call the shortest_path_as_geometry function on each row
> that comes back from that, then sum the length of those geometries.
>
> Approach #1:
>
> Change the sum_path_as_geometry function to take an array or row instead
> of three value and move the shortest_path_as_geometry function into the
> SELECT list, e.g.
>
> SELECT sum(length(sum_path_as_geometry(a.c1, a.c2, a.c3)))
> FROM ( SELECT c1, c2, c3 FROM ... ) a;
>
> Approach #2:
>
> Glue the other query and sum_path_as_geometry together with a new table
> function (i.e. a function that returns a set of rows). Define the new
> function to apply the sum_path_as_geometry function to the appropriate
> rows and then use the new function in your query:
>
> SELECT sum(length(the_geom)) FROM new_function();
>
>

Thanks for the ideas Harold. Here is the actual function definition,
it would be nice to make the changes that you suggested: allow this
function to work on rows of data instead of a single set of 3 values.

-----------------------------------------------------------------------
-- Compute the shortest path using edges and vertices table, and return
--  the result as a set of (gid integer, the_geom gemoetry) records.
-----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION shortest_path_as_geometry(geom_table
varchar, geom_source anyelement,
                                                     geom_target
anyelement) RETURNS SETOF GEOMS AS
$$
DECLARE
        r record;
        source int4;
        target int4;
        path_result record;
        v_id integer;
        e_id integer;
        geom geoms;
BEGIN
        FOR r IN EXECUTE 'SELECT id FROM ' || quote_ident(geom_table)
|| '_vertices WHERE geom_id = ' || quote_literal(geom_source) LOOP
                source = r.id;
        END LOOP;
        IF source IS NULL THEN
                RAISE EXCEPTION 'Can''t find source edge';
        END IF;

        FOR r IN EXECUTE 'SELECT id FROM ' || quote_ident(geom_table)
|| '_vertices WHERE geom_id = ' || quote_literal(geom_target) LOOP
                target = r.id;
        END LOOP;
        IF target IS NULL THEN
                RAISE EXCEPTION 'Can''t find target edge';
        END IF;

        FOR geom IN SELECT * FROM
shortest_path_as_geometry_internal_id(geom_table, source, target) LOOP
                RETURN NEXT geom;
                END LOOP;
        RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;


the above function then calls another function, which actually does
the work, but instead using internal id numbers:

CREATE OR REPLACE FUNCTION
shortest_path_as_geometry_internal_id(geom_table varchar,
                                                     source int4, target int4)
                                                     RETURNS SETOF GEOMS AS
$$
DECLARE
        r record;
        path_result record;
        v_id integer;
        e_id integer;
        geom geoms;
BEGIN

        FOR path_result IN EXECUTE 'SELECT vertex_id, edge_id FROM
shortest_path(''SELECT id, source, target, cost FROM ' ||
                quote_ident(geom_table) || '_edges '', ' ||
quote_literal(source) || ' , ' || quote_literal(target) || ' , false,
false) ' LOOP

                v_id = path_result.vertex_id;
                e_id = path_result.edge_id;

                FOR r IN EXECUTE 'SELECT gid, the_geom FROM ' ||
quote_ident(geom_table) || '  WHERE edge_id = ' || quote_literal(e_id)
LOOP
                        geom.gid := r.gid;
                        geom.the_geom := r.the_geom;
                        RETURN NEXT geom;
                END LOOP;
        END LOOP;
        RETURN;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;


Can these type of functions easily be generalized to work on a rows of
data instead of what they currently use?


any ideas would be greatly appreciated!

PS: these functions are part of a shortest path routing solution for
PostGIS, the spatial extension to PostgreSQL.

Cheers,

Dylan