Re: return MAX and when it happened

On Wed, Nov 19, 2008 at 05:06:14PM -0600, Scara Maccai wrote:
> Sam Mason wrote:
> >The custom aggregate sounds the
> >most elegant, it's just annoying that it's so much fiddling to get it
> >all working to start with
> Thanks.
>
> I think I wrote it, but there's something I don't get from the docs: do
> I have to call
>
> get_call_result_type(fcinfo, NULL, &tupdesc)

I've always tried to stay away from C level extensions so far!  How
many records are you expecting to aggregate across?  If it's only a few
thousand a simple SQL language function may be ok:

  CREATE TYPE nt AS ( n INTEGER, t TIMESTAMP );

  CREATE FUNCTION maxnt(nt, nt) RETURNS nt IMMUTABLE LANGUAGE SQL AS $$
    SELECT CASE WHEN $1.n > $2.n THEN $1 ELSE COALESCE($2,$1) END $$;

  CREATE AGGREGATE MAX (nt) (
      SFUNC = maxnt,
      STYPE = nt
  );

This is about 20 times slower than a C function (80 vs ~1500 rows per
ms), but if you're only iterating over a few rows it's not going to
matter much.

Sorry I can't be of more help!


  Sam