I have a plpgsql function in Postgres 12 that returns SETOF bytea
, which is then made into a single bytea
on the server:
CREATE OR REPLACE FUNCTION public.get_tile(z integer, x integer, y integer)
RETURNS SETOF bytea
LANGUAGE plpgsql
STABLE PARALLEL SAFE
AS $function$
BEGIN
RETURN QUERY EXECUTE FORMAT('
WITH bounds AS (
SELECT ST_TileEnvelope(%s, %s, %s) AS geom
)
SELECT
ST_AsMVT(mvtgeom, ''lyr_'' || layer_id, 4096, ''geom'', ''feature_id'')
FROM (
SELECT
ST_AsMVTGeom(t.geom, bounds.geom, 4096, 0) AS geom,
id AS feature_id,
layer_id
FROM my_geom_table t, bounds
WHERE ST_Intersects(t.geom, bounds.geom)
) mvtgeom
GROUP BY layer_id',
z, x, y
);
END;
$function$;
I was told the process could be more efficient if the concatenation was done within the query. I know that bytea
can be concatenated with ||
, but I don't know how a SETOF bytea
can be concatenated into a single one.
Btw, the reason for the multiple rows is to give each layer the correct name ('lyr' || layer_id
, which is column on the geometry table).
Is it possible to update this function to return a single row, which holds all the tile data for each layer?
string_agg(expression, delimiter)
can also aggregate bytea
.
CREATE OR REPLACE FUNCTION public.get_tile(z integer, x integer, y integer)
RETURNS bytea
LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
WITH bounds(geom) AS (SELECT ST_TileEnvelope(z,x,y))
SELECT string_agg(mvt, '')
FROM (
SELECT ST_AsMVT(mvtgeom, 'lyr_' || layer_id, 4096, 'geom', 'feature_id') AS mvt
FROM (
SELECT ST_AsMVTGeom(t.geom, bounds.geom, 4096, 0) AS geom
, id AS feature_id
, layer_id
FROM my_geom_table t, bounds
WHERE ST_Intersects(t.geom, bounds.geom)
) mvtgeom
GROUP BY layer_id
) sub;
$func$;
The RETURNS
clause changes to just bytea
. No SETOF
.
Not using any delimiter (''
), but you can if you need it.
Demonstrating a simplified SQL function.
It's a plain query. No need for dynamic SQL. No need for PL/pgSQL, even.