Anonymous record member access
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/sql-expressions.html
Description:
Hello again, Postgres!
(Background: I'm using Postgres 17.10, Debian stable, amd64.)
According to <https://stackoverflow.com/a/71960961>, the elements of
an anonymous ROW can be referenced with .f1 .f2 etc notation. This
cites "Row Constructors" of the Postgres manual (version 13, but it's
essentially unchanged from 13 to 19), and I don't see anything in that
section of the manual which would cause me to believe that .f1 will
return the first element of an anonymous record.
It says: "For example, if table t has columns f1 and f2...". All of
the examples below that have explicitly named "f1", "f2", etc fields,
too. There's nothing that indicates to me that this will work for
anonymous rows, i.e., when f1/f2 aren't explicitly defined. (The
anonymous .f1 trick is mentioned in the Postgres 13 release notes,
though.)
There's probably something I'm missing, but this feature appears to be
inconsistently supported. For example:
SELECT ROW(3,4,5); -- returns (3,4,5)
SELECT (ROW(3,4,5)).f1; -- returns 3
SELECT f1(ROW(3,4,5)); -- returns 3
CREATE FUNCTION f() RETURNS record AS $$
BEGIN
RETURN ROW(3,4,5);
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT f(); -- returns (3,4,5)
SELECT (f()).f1; -- error: could not identify column "f1" in record data
type
SELECT f1(f()); -- error: no function matches the given name and argument
types
Even stranger, to_json and to_jsonb (the only functions I see which
accept a generic RECORD) agree that these are the names of its fields,
in both cases:
SELECT to_json(ROW(3,4,5)); -- returns {"f1":3,"f2":4,"f3":5}
SELECT to_json(f()); -- returns {"f1":3,"f2":4,"f3":5}
Could there be some subtle distinction between ROW and RECORD? I
think RECORD is the type, and ROW is a constructor for anonymous
values. But even casting my ROW to RECORD (the same type as my
function "RETURNS") makes no difference:
SELECT pg_typeof(ROW(3,4,5)); -- record
SELECT pg_typeof(ROW(3,4,5)::record); -- record
SELECT pg_typeof(f()); -- record
SELECT (ROW(3,4,5)::record).f1; -- returns 3
SELECT (f()::record).f1; -- error: could not identify column "f1" in record
data type
Being able to access fields of an anonymous ROW would be useful. As I
see it, the only use for an anonymous ROW() now is to cast to an
existing (composite) type, or to pass to to_json() or to_jsonb() if
you happen to want a JSON dict with f1/f2/etc keys.
To summarize, things I don't understand from the docs:
- where exactly the .f1 notation is documented
- where/why it's not allowed to be used
- how/why a ROW changes behavior when returned from a FUNCTION
Thanks for listening!
- Ken
On Monday, September 14, 2026, PG Doc comments form <noreply@postgresql.org>
wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/sql-expressions.html
Description:To summarize, things I don't understand from the docs:
- where exactly the .f1 notation is documented
It probably isn’t documented behavior and thus should not be relied upon.
- where/why it's not allowed to be used
- how/why a ROW changes behavior when returned from a FUNCTION
I suspect these are just boundary issues that would be considered bugs if
this entire thing were considered documented behavior.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Monday, September 14, 2026, PG Doc comments form <noreply@postgresql.org>
wrote:To summarize, things I don't understand from the docs:
- where exactly the .f1 notation is documented
It probably isn’t documented behavior and thus should not be relied upon.
Yeah, it's not documented in any user-facing place, AFAICS. That's
because it's not a feature so much as a collection of legacy behaviors.
- where/why it's not allowed to be used
- how/why a ROW changes behavior when returned from a FUNCTION
I suspect these are just boundary issues that would be considered bugs if
this entire thing were considered documented behavior.
The key thing to understand is static versus dynamic typing.
If foo() is declared to return a named composite type, then
when you write
SELECT (foo(...)).x
the parser can look up the composite type and identify that yes,
x is a column of that type, it has position n and data type so-and-so,
and then it knows the result type of that expression and can continue
parsing. But if foo() is declared to return record then no such
information is available, and the parser must throw up its hands.
The function might return one thing today and something entirely
different tomorrow.
ROW() constructors do need to assign column names in the
anonymous record type they construct, and what they use is
indeed f1,f2,etc. But for most purposes in SQL you can't
see that because SQL is mostly a statically-typed language,
so it can't do much with an anonymous record value other
than pass it around. There are functions like to_json()
that are declared to accept type record, which means that
they can work on any composite type whatever. They use
below-SQL-level implementation details to find out what are
the column names and datatypes inside whatever they're handed.
You can do some of this stuff in plpgsql or other PLs, which
are less resolute about being statically typed than the main
SQL grammar. There are also behaviors that are just plain
warts, such as your example
SELECT (ROW(3,4,5)).f1; -- returns 3
On what I've been telling you, that ought to fail. It does
work, because the parser logic that looks up the composite
type of the left-hand side of a field accessor dot has a
special case for when that left-hand side is exactly a RowExpr.
(I think yours truly might be responsible for that, but it's
still a wart.)
So it's all pretty messy and no one has cared to try to make it
coherent enough to be document-able. I join with David in
recommending that you avoid relying on this. If we ever did
try to make it coherent, we'd likely elect to break some
behaviors that happen to work today.
regards, tom lane