Function Layer using plpgsql Not Working

[andrewlee8247]

I am trying to build a function that applies hexagon based clustering (using H3) which will filter by attributes (like CQL filter) in addition to an AOI. The initial function that only uses an AOI works when the language is set to sql, but the same function does not work when set to plpgsql.

This does not work:

 CREATE OR REPLACE FUNCTION tenant.clusters(
    z integer,
    x integer,
    y integer,
    aoi text DEFAULT NULL::text
)
RETURNS bytea
LANGUAGE plpgsql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $$
DECLARE
    result bytea;
BEGIN
    WITH bounds AS (
        SELECT ST_TileEnvelope(z, x, y) AS geom
    ), 
   mvtgeom AS (
        SELECT 
            ST_AsMVTGeom(h.centroid, b.geom) AS geom,
            h.resolution,
            CASE 
                WHEN aoi IS NOT NULL THEN COUNT(*)
                ELSE h.count
            END AS point_count
        FROM 
            tenant.hexagons h
        LEFT JOIN 
            tenant.properties p
        ON 
            ST_Intersects(h.geom, p.geom)
            AND ST_Intersects(p.geom, ST_Simplify(ST_Transform(ST_SetSRID(ST_GeomFromText(COALESCE(aoi, 'POLYGON EMPTY')), 4326), 3857), 0.05, true))
        JOIN 
            bounds b ON ST_Intersects(h.geom, b.geom)
        WHERE 
            h.resolution = tenant.set_resolution(z)
            AND (aoi IS NULL OR ST_Intersects(h.geom, ST_Simplify(ST_Transform(ST_SetSRID(ST_GeomFromText(aoi), 4326), 3857), 0.05, true)))
            AND z <= 14
        GROUP BY 
            h.centroid, b.geom, h.resolution, h.count
    )
    SELECT ST_AsMVT(mvtgeom, 'tenant.clusters')
    INTO result FROM mvtgeom;

    RETURN result;
END;
$$;

However this works:

CREATE OR REPLACE FUNCTION tenant.clusters(
	z integer,
	x integer,
	y integer,
	aoi text DEFAULT NULL::text)
    RETURNS bytea
    LANGUAGE 'sql'
    COST 100
    STABLE PARALLEL SAFE 
AS $BODY$
    WITH bounds AS (
        SELECT ST_TileEnvelope(z, x, y) AS geom
    ),
    mvtgeom AS (
        SELECT 
            ST_AsMVTGeom(h.centroid, b.geom),
			h.resolution,
             CASE 
                WHEN aoi IS NOT NULL THEN COUNT(*)
                ELSE h.count
             END AS point_count
        FROM 
            tenant.hexagons h
        LEFT JOIN 
            tenant.properties p
        ON 
            ST_Intersects(h.geom, p.point)
			AND ST_Intersects(p.point, ST_Simplify(ST_Transform(ST_SetSRID(ST_GeomFromText(COALESCE(aoi, 'POLYGON EMPTY')), 4326), 3857), 0.05, true))
		JOIN
			bounds b ON ST_Intersects(h.geom, b.geom)
        WHERE 
            h.resolution = tenant.set_resolution(z)
			AND (aoi IS NULL OR ST_Intersects(h.geom, ST_Simplify(ST_Transform(ST_SetSRID(ST_GeomFromText(aoi), 4326), 3857), 0.05, true)))
			AND z <= 14
        GROUP BY 
            h.centroid, b.geom, h.resolution, h.count
    )
    SELECT ST_AsMVT(mvtgeom, 'tenant.clusters')
    FROM mvtgeom
$BODY$;

Am I doing something wrong? I am following the documentation on functions using plpgsql from here:
https://access.crunchydata.com/documentation/pg_tileserv/latest/pdf/pg_tileserv.pdf

Any help is much appreciated. Thank you!

[sabman]

try removing:

-- IMMUTABLE  -- Remove this line
-- STRICT  -- Remove this line

IMMUTABLE means that the function is pure and doesn't change results if the input is the same. This particular function can't be IMMUTABLE because it does select from external tables.

STRICT causes the function to return NULL if any of the inputs are NULL. Since the default value of aoi is NULL it might return NULL without executing the function.

[dr-jts]

What doesn't work for the first function? And if the second version works, why not just use that?