postgresql - 函数中的参数导致问题

Question

我有一些代码返回行驶时间的多边形:

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;

这已经过测试并且有效。我试图将其包装在一个函数中并传递参数以替换 1946600 和 10 值

函数如下所示:

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;
$$ 

但是它不起作用。

如果我用硬编码值替换源和步行时间的参数，那么该函数就可以工作。

我读过一些关于必须将它变成执行语句的内容，但有人能给我指出正确的方向吗？

Answer 1

查询是一个字符串，因此您需要设置占位符，然后从变量中设置值。您将使用 format 和 %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)