Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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)

- tye        


In reply to Re^3: Help with removing dupes from a string with perl (last) by tye
in thread Help with removing dupes from a string with perl by CG_man

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • 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.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2024-03-28 12:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found