3

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
2
  • 1
    "But how can I get the fields out again" - you can't unless you cast the anonymous record to a know (record) type. Aug 11 '20 at 9:36
  • See ROW constructors, especially its example code.
    – Akina
    Aug 11 '20 at 11:52
6

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.

1
  • Somehow I wasn't able to understand the original documentation. before reading this question and your explanation. Thanks Peter! Oct 7 '21 at 1:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.