postgresqlaggregate-functionspostgisplpgsqlbytea

Concat setof bytea into single bytea


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?


Solution

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