Skip site navigation (1) Skip section navigation (2)

Re: writing a MIN(RECORD) aggregate

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: writing a MIN(RECORD) aggregate
Date: 2008-03-25 20:22:02
Message-ID: 20080325202202.GJ6870@frubble.xen.chris-lamb.co.uk (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Mar 25, 2008 at 07:54:17PM +0000, Gregory Stark wrote:
> "Sam Mason" <sam(at)samason(dot)me(dot)uk> writes:
> > SELECT i, (MIN((j,k))).k AS ka, (MIN((mycode(j),k))).k AS kb
> > FROM tbl
> > GROUP BY i;
> 
> The flip side is that if you want to get several fields based on min(j) the
> min(record) approach requires you to write that expression several times (and
> the database to calculate it several times).

No.  My demos have only used one column because that's the smallest
useful demo.

  SELECT i, r.k, r.l
  FROM (
    SELECT i, MIN((j,k,l)) AS r
    FROM tbl
    GROUP BY i) x;

The reason for the sub-select is only because SQL doesn't provide any
other way to name expressions.  Hum, or at least this should work...
There doesn't seem to be any nice way of getting fields out of a record!

If I really want to do this, it's going to turn into quite an overhaul
of record handling in PG.  It would also remove the nice syntactic trick
that a.b identifies the field "b" from table "a", and s.a.b means that
the above is in schema "s".

> I think the window functions might (assuming an ideal implementation) get the
> best of both worlds. You would be able to do something with multiple
> partitions so you could ask of a few columns where rank over j = 1 and a few
> more columns where rank over k = 1.
>
> But, uh, I'm not sure. I'll have to sit down with the spec and see if that's
> true. Furthermore it may be wishful thinking to hope that the implementation
> will do anything special with the special case where you're only selecting
> records where rank = 1.

I don't really understand what you're saying above.  Optimisation is
another can of worms that shouldn't be opened until we know how this
sort of thing is going to be used.


  Sam

In response to

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2008-03-25 20:25:40
Subject: Re: GiST opclass and varlena
Previous:From: Alvaro HerreraDate: 2008-03-25 20:21:37
Subject: Re: libpq type system 0.9a

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group