agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedAnonymous record member access
5+ messages / 5 participants
[nested] [flat]
* Anonymous record member access
@ 2026-09-14 18:39 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Doc comments form @ 2026-09-14 18:39 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: kengruven@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Anonymous record member access
@ 2026-09-15 12:29 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2026-09-15 12:29 UTC (permalink / raw)
To: kengruven@gmail.com <kengruven@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Anonymous record member access
@ 2026-09-15 14:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-09-15 14:19 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: kengruven@gmail.com <kengruven@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Anonymous record member access
@ 2026-09-20 18:36 Ken Harris <kengruven@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Ken Harris @ 2026-09-20 18:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Thanks for the info. I guess my big lesson here is: (plpg)sql is much more
statically typed than I thought.
I was hoping for a function like:
CREATE FUNCTION field_for_composite_value(record, text) RETURNS any AS $$
return record[text]
$$ LANGUAGE plpython3u IMMUTABLE STRICT;
but pretty much all of the "pseudo-types" are unavailable to all four of
the built-in extension languages. (In fact, "any" is so unsupported that
it's a syntax error here!) I guess I need to figure out C extensions for
that.
The bigger picture is that I wanted to write something kind of like
to_json() which dumps composite values recursively, but with a different
format. (There are also *_to_xml() functions, but unlike JSON, they output
composite values as plain strings like (a,,,b) -- and also only operate on
cursor/table/query/database, not individual values.) I'm still playing
with these, and I'm not sure if I can use them to fake a
`field_for_composite_value()` function or not.
Thanks again.
- Ken
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Anonymous record member access
@ 2026-09-20 19:31 Pavel Stehule <pavel.stehule@gmail.com>
parent: Ken Harris <kengruven@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2026-09-20 19:31 UTC (permalink / raw)
To: Ken Harris <kengruven@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Hi
ne 20. 9. 2026 v 20:36 odesílatel Ken Harris <kengruven@gmail.com> napsal:
>
> Thanks for the info. I guess my big lesson here is: (plpg)sql is much
> more statically typed than I thought.
>
PostgreSQL is a very static system - there is a basic rule - the complete
structure of the result must be known in planning time - before query
execution.
There can be some exceptions inside some subsystems - but at the level of
top query, this rule is absolute. There are partial solutions - you can use
json, xml, hstore, arrays,
but it is always partial solution.
Most SQL databases are "compilers" - and it is reason, why these systems
are static.
Some other databases are "dynamic" in execution of SQL procedures executed
by CALL statement. CALL statement has not execution plan, so it is
possible. But PostgreSQL stored procedures cannot to return recordset, so
at the end, it is static too (on Postgres).
Generally, don't try to design too dynamic system inside PostgreSQL stored
procedures. It doesn't work (without own dedicated extensions). But still
extensions cannot to force dynamic result structure to top query.
Regards
Pavel
> I was hoping for a function like:
>
> CREATE FUNCTION field_for_composite_value(record, text) RETURNS any AS $$
> return record[text]
> $$ LANGUAGE plpython3u IMMUTABLE STRICT;
>
> but pretty much all of the "pseudo-types" are unavailable to all four of
> the built-in extension languages. (In fact, "any" is so unsupported that
> it's a syntax error here!) I guess I need to figure out C extensions for
> that.
>
> The bigger picture is that I wanted to write something kind of like
> to_json() which dumps composite values recursively, but with a different
> format. (There are also *_to_xml() functions, but unlike JSON, they output
> composite values as plain strings like (a,,,b) -- and also only operate on
> cursor/table/query/database, not individual values.) I'm still playing
> with these, and I'm not sure if I can use them to fake a
> `field_for_composite_value()` function or not.
>
> Thanks again.
>
>
> - Ken
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-09-20 19:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-14 18:39 Anonymous record member access PG Doc comments form <noreply@postgresql.org>
2026-09-15 12:29 ` David G. Johnston <david.g.johnston@gmail.com>
2026-09-15 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-20 18:36 ` Ken Harris <kengruven@gmail.com>
2026-09-20 19:31 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox