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

Re: First Aggregate Funtion?

From: "Mike Rylander" <mrylander(at)gmail(dot)com>
To: "Martijn van Oosterhout" <kleptog(at)svana(dot)org>
Cc: "Tony Caduto" <tony_caduto(at)amsoftwaredesign(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: First Aggregate Funtion?
Date: 2006-04-01 01:35:35
Message-ID: b918cf3d0603311735v5dde21d4pfcd613c09d80cf57@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 3/31/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Fri, Mar 31, 2006 at 03:02:47PM -0600, Tony Caduto wrote:
> > Has there ever been any talk of adding a first aggregate function?
> > It would make porting from Oracle and Access much easier.
> >
> > Or is there something in the contrib modules that I might have missed?
>
> There are several oracle compatability modules:
>
> http://pgfoundry.org/projects/oracompat/
> http://pgfoundry.org/projects/orafce/
>
> I'm sure there's many more if you look...

If all you want is FIRST() and LAST() then:

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
        SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$$ LANGUAGE SQL STABLE;

-- And then wrap an aggreagate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
        SELECT $2;
$$ LANGUAGE SQL STABLE;

-- And then wrap an aggreagate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);


Hope that helps!

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org

In response to

pgsql-hackers by date

Next:From: Neil ConwayDate: 2006-04-01 01:42:02
Subject: Re: listen not schema-aware
Previous:From: David FetterDate: 2006-04-01 01:31:10
Subject: Re: Suggestion: Which Binary?

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