Re^3: Help with removing dupes from a string with perl (last)

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
);
[download]

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;
[download]

produces the following results:

 first | first_nonnull | last | last_nonnull
-------+---------------+------+--------------
       | one           | two  | two
 one   | one           |      | two
(2 rows)
[download]

- tye

Comment on Re^3: Help with removing dupes from a string with perl (last) Select or Download Code

Replies are listed 'Best First'.
Re^4: Help with removing dupes from a string with perl (last) by tilly (Archbishop) on Jul 10, 2009 at 00:35 UTC
Sorry, no. Your solution is nothing like SQL 2003's analytic functions which come from Oracle. If you want a brief introduction to them, read http://www.orafusion.com/art_anlytc.htm. The syntax is..heinous. To use them effectively you often want to query a subquery. But they are very powerful and much more efficient than older solutions.	[reply]
Re^4: Help with removing dupes from a string with perl (last) by tye (Sage) on Jul 05, 2011 at 22:41 UTC
Actually, despite tilly's response, these are exactly like some of what Postgresql calls "window functions" which are exactly the same thing that Oracle calls "analytic functions" (Oracle uses "window functions" to refer to a subset of the "analytic functions"). One difference between my user-defined aggregate functions above and the standard window functions first_value() and last_value(), is that my aggregates can be used as simple aggregates (such as with just "GROUP BY") while first_value() and last_value() must have a window defined (via "OVER"). That is, my aggregates can be used with OVER or without OVER. But the main reason I am responding is to note that my third attempt at defining first() and last() was much simpler. It is just like the _nonnull versions except you drop the word 'strict'. `create function first( one anyelement, two anyelement ) returns anyelement as $$ begin return one; end $$ language plpgsql; create function last( one anyelement, two anyelement ) returns anyelement as $$ begin return two; end $$ language plpgsql; create aggregate first( anyelement )( stype = anyelement , sfunc = first ); create aggregate last( anyelement )( stype = anyelement , sfunc = last );` [download] Which makes me wonder what my first try (that didn't work) looked like. I'm now working with newer Postgresql that supports window functions. But this version's first_value() doesn't support the standard "IGNORE NULLS" option which looked like the best solution for a problem I was working on today. So I asked google where my implementations were so I could just use them. Swapping in these details again made me suspect that the first() and last() implementations could be as simple as the first_nonnull() and last_nonnull() implementations. Testing on this newer version of Postgresql showed that they could be. - tye	[reply] [d/l]