parameters in a function causing problems

Question

I have some code which returns a polygon for drivetime:

SELECT ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),4326) AS geometry
FROM (
    SELECT ST_Makeline(points ORDER BY id) AS openline
    FROM (
        SELECT row_number() over() AS id, ST_MakePoint(x, y) AS points 
        FROM pgr_alphashape('
            SELECT *
            FROM "UKRoads".node as node
        JOIN
            (SELECT * FROM pgr_drivingDistance(''
            SELECT id,
            source::int4 AS source,
            target::int4 AS target,
            cost_walk_m::float8 AS cost,
            cost_walk_m::float8 AS reverse_cost
            FROM public.hh_2po_4pgr'',
            1946600,
            10,
            false,
            false)) AS dd ON node.id = dd.id1'::text)
  ) AS a
) AS foo;

This has been tested and works. I'm trying to wrap this in a function and pass parameters to replace the 1946600 and 10 values

The function looks like:

CREATE OR REPLACE FUNCTION usp_walkingrange(source integer, walkingtime integer) RETURNS geometry 
LANGUAGE plpgsql
AS $$

DECLARE
    retRange geometry:=null;
    BEGIN
        SELECT ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),4326) AS geometry
        into retRange
        FROM (
            SELECT ST_Makeline(points ORDER BY id) AS openline
            FROM (
                SELECT row_number() over() AS id, ST_MakePoint(x, y) AS points 
            FROM pgr_alphashape('
                SELECT *
                FROM "UKRoads".node as node
                JOIN
                (SELECT * FROM pgr_drivingDistance(''
                    SELECT id,
                    source::int4 AS source,
                    target::int4 AS target,
                    cost_walk_m::float8 AS cost,
                    cost_walk_m::float8 AS reverse_cost
                    FROM public.hh_2po_4pgr'',
                    source,
                    walkingtime,
                    false,
                    false)) AS dd ON node.id = dd.id1'::text)
             ) AS a
        ) AS foo;

        RETURN retRange;
        END;
$$ 

However it doesn't work.

If I replace the parameters for source and walking time with hardcoded values then the function works.

I've read something about having to make it into an execute statement but can anyone point me in the right direction?

Answer 1

The query is a string, so you need to set placeholders and then set the value from the variable. You would use format with the %s placeholder:

 [...]
 FROM pgr_alphashape(format('
    SELECT *
    FROM "UKRoads".node as node
    JOIN
    (SELECT * FROM pgr_drivingDistance(''
        SELECT id,
        source::int4 AS source,
        target::int4 AS target,
        cost_walk_m::float8 AS cost,
        cost_walk_m::float8 AS reverse_cost
        FROM public.hh_2po_4pgr'',
        %s,
        %s,
        false,
        false)) AS dd ON node.id = dd.id1',source,walkingtime)::text)