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:

    in_geom GEOMETRY

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

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

        UNION ALL

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

        ORDER BY



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.

1 Answer 1


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
    Commented Nov 22, 2017 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
    Commented Nov 22, 2017 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
    – geozelot
    Commented Nov 22, 2017 at 12:23
  • @ThingumaBob Ah yes, good catch. It works for select * from myfunction(param), not if embeded in a sub-query
    – JGH
    Commented Nov 22, 2017 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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