Pass geometry as input to custom PostGIS function

Question

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.

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;