Aggregates with non-commutative transition functions
От | Emmanuel Charpentier |
---|---|
Тема | Aggregates with non-commutative transition functions |
Дата | |
Msg-id | b2grsk$2r7t$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: Aggregates with non-commutative transition functions
|
Список | 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
В списке pgsql-general по дате отправления: