Naming row fields without explicit type specification

[EvgSkv]

Is there a way to name fields of a row without specifying their types?
Like this

select cast(row(1,'hello') as row(x bigint, y varchar));

but if I don't want to specify bigint and varchar?

It seems that it could be technically possible, as Trino can operate on unnamed rows inferring type from the context.
Is there a way to do so?

I'm asking because we are generating SQL and do not have type information at the moment.

[EvgSkv]

An answer that this is not possible would also help :-)

[martint]

This is currently not possible. I'm looking at the SQL specification to see if there's anything we're missing that would allow this, but there doesn't seem to be any standard functionality for this that we could implement.

[martint]

One option, which unfortunately is not currently supported in Trino, would be to use a scalar subquery that produces a ROW type:

SELECT (SELECT 1 AS x, 2 AS y)

[martint]

I've filed #7773

[martint]

And here's the implementation: #7774

trino> SELECT typeof((SELECT 1 AS x, 'hello' AS y)), (SELECT 1 AS x, 'hello' AS y);
            _col0             |     _col1
------------------------------+----------------
 row(x integer, y varchar(5)) | {x=1, y=hello}
(1 row)

[EvgSkv]

Wow. Thank you very much for doing this! This hugely helps my use-case.

[johnedquinn]

Interesting, is there any way to write a subquery consisting of a single projection item to still be of a ROW type? AKA to not be coerced into a scalar?

SELECT (SELECT 1 AS x)

I'd also like to create a ROW with named fields without having to specify its type.