0

I am desigining a custom PostGIS function and I am at a loss on how to pass a geometry column from a table as an input to another column.

I am writing a function to return certain columns from a table, and overwrite their cost_s and reverse_cost_s values if they intersect an input geometry.

This is what I have so far:

CREATE OR REPLACE FUNCTION replace_cost(
    in_geom GEOMETRY
)

    RETURNS TABLE(
        class_id INTEGER,
        name VARCHAR(255),
        cost_s DOUBLE PRECISION,
        reverse_cost_s DOUBLE PRECISION
    ) AS

    $BODY$
        SELECT
            a.class_id AS class_id,
            a.name AS name,
            a.cost_s AS cost_s,
            a.reverse_cost_s AS reverse_cost_s
        FROM
            ways AS a
        WHERE 
            /*a.class_id != test_input*/
            NOT ST_Intersects(in_geom, a.the_geom)

        UNION ALL

        SELECT
            a.class_id AS class_id,
            a.name AS name,
            'INFINITY' AS cost_s,
            'INFINITY' AS reverse_cost_s
        FROM
            ways AS a
        WHERE 
            /*a.class_id = test_input*/
            ST_Intersects(in_geom, a.the_geom)

        ORDER BY
            cost_s

    $BODY$

    LANGUAGE SQL;


SELECT * FROM replace_cost(overwrite_poly.geom);

But I get the error back:

ERROR:  missing FROM-clause entry for table "overwrite_poly"
LINE 57: SELECT replace_cost(overwrite_poly.geom);

I think I kind of understand, because I don't have overwrite_poly in any kind of FROM clause, but I don't know how or where to fit it in. I also can't find much in the way of writing functions that take a PostGIS geom as input.

2

The function looks fine, the issue is in the way you call it. You would need to select from the said table, like:

SELECT replace_cost(overwrite_poly.geom)
FROM overwrite_poly
WHERE overwrite_poly.ID =123;
  • Thanks for the response, this does indeed work, but the response I get looks strange, it appears to be a single column containing individual records: "(114,Zähringerstraße,Infinity,Infinity)" How do I get it to return like a normal table? – wfgeo Nov 22 '17 at 11:36
  • 1
    @1saac Read this Basically you need to select from your output table, so either SELECT (replace_cost(overwrite_poly.geom)).* from... or select * from (SELECT replace_cost(overwrite_poly.geom) from .. ) – JGH Nov 22 '17 at 12:15
  • @JGH AFAIK the second one does return the same set structure as well, not the column wise structure...first one works fine – ThingumaBob Nov 22 '17 at 12:23
  • @ThingumaBob Ah yes, good catch. It works for select * from myfunction(param), not if embeded in a sub-query – JGH Nov 22 '17 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.