On 7/22/24 15:43, Tom Lane wrote:
Isaac Morland <isaac.morl...@gmail.com> writes:
And for when this might be useful, the syntax for it already exists,
although a spurious error message is generated:
odyssey=> select (uw_term).*, count(*) from uw_term group by uw_term;
ERROR: column "uw_term.term_id" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: select (uw_term).*, count(*) from uw_term group by uw_term;
^
I'm not sure exactly what's going on here
The SELECT entry is expanded into "uw_term.col1, uw_term.col2,
uw_term.col3, ...", and those single-column Vars don't match the
whole-row Var appearing in the GROUP BY list. I guess if we
think this is important, we could add a proof rule saying that
a per-column Var is functionally dependent on a whole-row Var
of the same relation. Odd that the point hasn't come up before
(though I guess that suggests that few people try this).
I was just using this group-by-row feature last week to implement a temporal outer join in a way
that would work for arbitrary tables. Here is some example SQL:
https://github.com/pjungwir/temporal_ops/blob/b10d65323749faa6c47956db2e8f95441e508fce/sql/outer_join.sql#L48-L66
That does `GROUP BY a` then `SELECT (x.a).*`.[1]
It is very useful for writing queries that don't want to know about the
structure of the row.
I noticed the same error as Isaac. I worked around the problem by wrapping it in a subquery and
decomposing the row outside. It's already an obscure feature, and an easy workaround might be why
you haven't heard complaints before. I wouldn't mind writing a patch for that rule when I get a
chance (if no one else gets to it first.)
[1] Actually I see it does `GROUP BY a, a.valid_at`, but that is surely more than I need. I think
that `a.valid_at` is leftover from a previous version of the query.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
