Create, Insert, and return BYTEA value

Question

I'm creating vector tiles using ST_AsMVT and I would like to store them immediately after they are created in addition to returning the object.

The below code works fine at creating a bytea response.

WITH mvtgeom AS
(
SELECT ST_AsMVTGeom(
ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
4096, 0, false)
)
SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom

But when I change the last section to include an INSERT statement:

INSERT INTO test SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom

I get the response:

This result object does not return rows

Which is of course true, but how to insert data which isn't a row?

The table test only has an ID and a BYTEA column.

Answer 1

Spell out the target column:

...
INSERT INTO test(bytea_column) -- use undisclosed column name
SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom
RETURNING bytea_column;

Else, the INSERT inserts into the (leading) ID column you mentioned.

The added RETURNING clause covers:

... in addition to returning the object.

Or provide a value for the ID column additionally, and (optionally) return the whole row:

...
INSERT INTO test(id, bytea_col) -- use undisclosed column names
SELECT 1, ST_AsMVT(mvtgeom.*)
FROM mvtgeom
RETURNING *;