How to extract fields of anonymous row type in PostgreSQL

Question

In PostgreSQL I can create an anonymous row or composite type

postgres=# SELECT ROW(1,'Hello',false) as r;
      r
-------------
 (1,Hello,f)
(1 row)

But how can I get the fields out again? None of the following work

postgres=# SELECT (r).* from (SELECT ROW(1,'Hello',false) as r) as T;
ERROR:  record type has not been registered

postgres=# SELECT (r).column0 from (SELECT ROW(1,'Hello',false) as r) as T;
ERROR:  record type has not been registered

Answer 1

New in v13

• Allow ROW expressions to have their members extracted with suffix notation (Tom Lane)

  For example, (ROW(4, 5.0)).f1 now returns 4.

select (ROW(1,'Hello',false)).f1

Note there are some gotchas. You may need to explicitly cast type

select (ROW(1,'Hello',false)).f2

ERROR: failed to find conversion function from unknown to text

select (ROW(1,'Hello'::text,false)).f2

See db<>fiddle for more 🙂

The relevant commit in source appears to be 8b7a0f1d11 with a discussion at https://www.postgresql.org/message-id/flat/10872.1572202006%40sss.pgh.pa.us

I don't know at this time of any official documentation, but I'll shout out Jack@TA for showing me the new trick.