Concat setof bytea into single bytea

Question

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?

score 2 · Accepted Answer · answered Jun 08 '20 at 00:04

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 RETURNSclause 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.

Concat setof bytea into single bytea

1 Answers1