Re: return MAX and when it happened - Mailing list pgsql-general

From Sam Mason
Subject Re: return MAX and when it happened
Date
Msg-id 20081120112920.GI2459@frubble.xen.chris-lamb.co.uk
Whole thread Raw
In response to Re: return MAX and when it happened  (Scara Maccai <m_lists@yahoo.it>)
Responses Re: return MAX and when it happened  (Scara Maccai <m_lists@yahoo.it>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Craig Ringer
Date:
Subject: Re: transaction_read_only effects on performance, actual meaning (was: How to reduce impact of a query)
Next
From: "Jonatan Evald Buus"
Date:
Subject: Fetch query which triggered a rule