Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: Anonymous record member access
To: pgsql-docs@lists.postgresql.org
From: PG Doc comments form <noreply@postgresql.org>
Cc: kengruven@gmail.com
Reply-To: kengruven@gmail.com, pgsql-docs@lists.postgresql.org
Date: Mon, 14 Sep 2026 18:39:09 +0000
Message-ID: <178941114936.1263.15482664434063598995@wrigleys.postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/178941114936.1263.15482664434063598995%40wrigleys.postgresql.org>
Precedence: bulk

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