ST_DUMP

[ebocher]

Add ST_DUMP function as https://postgis.net/docs/ST_Dump.html

Not sure if H2 is today able to support a set-returning function

@katzyn

[katzyn]

H2 supports user-defined table value functions exactly is the same way as other user-defined functions, they simply need to return a java.sql.ResultSet.

[ebocher]

We use it. But not sure if we can apply the same sql syntaxe as

SELECT sometable.field1, sometable.field1,
      (ST_Dump(sometable.the_geom)).geom AS the_geom
FROM sometable;

or

SELECT (a.p_geom).path[1] As path, ST_AsEWKT((a.p_geom).geom) As geom_ewkt
  FROM (SELECT ST_Dump(ST_GeomFromEWKT('POLYHEDRALSURFACE(
((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),  ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),  ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1))
)') ) AS p_geom )  AS a;

[katzyn]

SELECT something FROM table_function(…) should work, other use cases aren't valid.

Current H2 also allows table value functions as plain expressions, such as SELECT table_function(…), but such support is a side effect of poorly designed implementation and it can be removed in the future versions.

SELECT (table_function()).something is not supported and is not going to be supported, such syntax in the SQL Standard is valid only for row value functions and is not yet supported by H2 even for them.

[ebocher]

Thanks @katzyn We are going to be vigilant on the use of the table functions