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
