PostGIS generate center POINTS from multilinestrings and multipolygons (for each line and polygon) in the memory

Question

Let's assume, I have a mixed geometry type table in PostgreSQL The multipart geometries don't intersect themselves. I would like to generate a center point collection from every geometry. I know how to create centroids from singlepart geoms. But is it possible to create singlepart geoms from multipart geoms without making a new table (ST_DUMP)? And in the memory take every converted geom, and get its center point. Is it possible?

Answer 1

With a LATERAL table function expression (as implicit CROSS JOIN) instead of record notation selection ((ST_Dump()).*) - flexible, fast and elegant:

SELECT t.<id>,
       ST_Centroid(dmp.geom) AS geom    -- ST_PointOnSurface(dmp.geom)
FROM   <table> AS t,
       LATERAL ST_Dump(t.<geom>) AS dmp
;