Skip to content

PostgreSQL banter on ‘record pseudotype’

dmg_83 Okay. I notice that if you a table like CREATE TABLE tbl (x int, y int) and do SELECT (tbl).x, (tbl).y FROM tbl WHERE tbl = ROW(1,2) you’ll indeed get back 1, 2 (assuming such a row exists). That seems a bit weird, since the left side of the expression “tbl = ROW(1,2)” does not seem to be the same as the right, for we can do (tbl).x, but can’t do (ROW(1,2)).x; tbl contains some additional data that ROW(1,2) does not

dmg_83 I’d like to understand the exact rules PG uses for these. I’m guessing it’s similar to something like how python compares a namedtuple and an ordinary tuple (disregarding the names and treating both as tuples for comparison sakes)

RhodiumToad row(1,2) actually uses column names “f1” and “f2” internally but that’s quite difficult to determine (and usually impossible to make use of)
  you can’t do (row(1,2)).f1 for example
  anyway, tbl = row(1,2) works because there’s a generic (record = record) operator defined
  and any composite type can implicitly cast to the “record” pseudotype
  the operator actually looks up the structure of the records at runtime (cached between calls from a single call site)
  the number and types of fields has to be acceptable, but the column names are ignored