Heh, I just rolled my own first() and last() aggregators in Postgresql yesterday because I wanted something like this (googling for analytic queries found a somewhat related link that mentioned first() and last() aggregators as I was curious if my solution was in-line with SQL 2003's solution).
The process was a bit more interesting and difficult than I expected. It convinced me that Postgresql doesn't have a general solution for user-defined polymorphic aggregators because it doesn't allow polymorphic user-defined data types (you can't use "anyelement" as part of a "create type" construct -- at least in the versions I was dealing with, 8.2 and 8.3).
So I was able to create some polymorphic aggregators but can see cases where these approaches would not work (and the first route I tried failed).
create function first_nonnull( one anyelement, two anyelement )
returns anyelement
as $$
begin
return one;
end
$$
language plpgsql strict;
create function last_nonnull( one anyelement, two anyelement )
returns anyelement
as $$
begin
return two;
end
$$
language plpgsql strict;
create aggregate first_nonnull( anyelement )
(
stype = anyelement
, sfunc = first_nonnull
);
create aggregate last_nonnull( anyelement )
(
stype = anyelement
, sfunc = last_nonnull
);
create function first( head anyarray, tail anyelement )
returns anyarray
as $$
begin
if head is null or head = '{}' then
return ARRAY[tail];
end if;
return head;
end
$$
language plpgsql;
create function last( head anyarray, tail anyelement )
returns anyarray
as $$
begin
return ARRAY[tail];
end
$$
language plpgsql;
create function first( head anyarray )
returns anyelement
as $$
begin
return head[1];
end
$$
language plpgsql;
create aggregate first( anyelement )
(
stype = anyarray
, initcond = '{}'
, sfunc = first
, finalfunc = first
);
create aggregate last( anyelement )
(
stype = anyarray
, initcond = '{}'
, sfunc = last
, finalfunc = first
);
The following test query:
select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select NULL::text as v union all
select 'one' as v union all select 'two' as v ) as q
union all
select first(v), first_nonnull(v), last(v), last_nonnull(v)
from ( select 'one'::text as v union all
select 'two' as v union all select NULL as v ) as q;
produces the following results:
first | first_nonnull | last | last_nonnull
-------+---------------+------+--------------
| one | two | two
one | one | | two
(2 rows)
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.