On Thu, Jul 10, 2025 at 7:11 PM Ron Johnson <ronljohnso...@gmail.com> wrote: > On Thu, Jul 10, 2025 at 12:26 PM Adrian Klaver <adrian.kla...@aklaver.com> > wrote: >> On 7/10/25 04:48, Dominique Devienne wrote: >> > Seems so logical to me, that these hashing functions were available >> > are aggregates, I can't be the first one to think of that, can it? >> >> I've been on this list since late 2002 and I don't recall this ever >> being brought up. Now it is entirely possible that age has dimmed my >> recall abilities:) Though a quick search seems to confirm my memory. > > What even is an aggregate hash function? (I can imagine a few possibilities, > but don't want to assume.)
Well, it's so obvious to me, I wonder if you're baiting me :) Any hasher/digest inits some internal state, processes bytes, typically in "streaming-fashion" via successive byte spans (equivalent to PostgreSQL's bytea), and yields a digest of various length at the end. The current md5() and pgcrypto.digest() functions roll the x1 init, xN process, and x1 finish into a single call, processing a single bytea (or perhaps more intelligently for TOAST'ed values, the 2K "rows" of those in streaming-fashion, hopefully. Can a dev confirm?). As an aggregate, the processing is extended to all values aggregated. That's it. Obviously order-sensitive, so an explicit ORDER BY inside the aggregate call is DE RIGEUR, but that's normal. As I mentioned already, SQLite supports sha3_agg() for almost a year, and had sha(1|3)_query, which is conceptually similar (although hashes value types too, since multi-column and dynamically typed), for years (8+ for sha3, probably decades for sha1). Basically anyone who knows hashing/digests and has ever written an aggregate UDF (in SQLite or elsewhere), understands what I'm talking about. --DD
