Aggregates with non-commutative transition functions - Mailing list pgsql-general

Dear list,

I am working on a bibliograpic database. Some citations come from Medline,
whose "full" format gives citations as an (ordered) set of tag-value pairs.
Among them, authots are quoted one tag-pair per author. Collecting them is
trivial, giving a table whose structure is essentially as in :

create cit_authors (
    recnum int4, -- Currrent record (citation)
    linenum int4, -- current line in input file, gives ordering
    author text, -- Author name and initials
    primary key (recnum, linenum));

This table has secondary indexes on both recnum and linenum, for various
query efficiency reasons ... In some case, a third index on author might
prove useful ...

In order to build the authors list of a given reference, I built an
auxilliary aggregate :

create function txt_glue (text, text) returns text as '
declare
   t1 alias for $1;
   t2 alias for $2;
   res text;
begin
   if t1 is null
      then
        res:=t2;
      elsif t2 is null
        then res:=t1;
      else res := t1 || \', \' || t2;
    end if;
   return res;
end;'
language plpgsql;

create aggregate glue ( basetype=text,
   sfunc=txt_glue,
   stype=text);

The problem is as follows : how can I guarantee that the authors will be
quoted in the original order ? In this case, text catenation is *highly*
noncommutative ! (<AsbestosLongjohns> Getting the authors order wrong is a
sure-fire way to get all the authors mad at you ... </AsbestosLongjohns>).

In other words, may I guarantee that :

select recnum, glue(linenum)as authors from (select recnum, linenum, author
from cit_authors where <some conditions on recnum> order by recnum,
linenum) as foo;

will indeed give me the authors in the original order ?

Your thoughs ?

                    Emmanuel Charpentier