Share your knowledge at the LQ Wiki.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 07-13-2005, 05:10 PM   #1
Senior Member
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,991

Rep: Reputation: 76
Postgresql aggregate text functions - concatenation, merge

In the hope that it will be useful, I would like to place this code in the public domain:

I find the following code, which is very Postgresql-specific, defines some insanely useful aggregate functions:
   -- Function form of || operator
   CREATE OR REPLACE FUNCTION catenate(text,text) RETURNS text AS '
      SELECT COALESCE($1 || $2,$1,$2,NULL)
   -- How to concatenate strings in an aggregate
   CREATE AGGREGATE concatenate (
      sfunc = catenate,
      basetype = text,
      stype = text,
      initcond = ''

   -- || function but with a two-element array
   CREATE OR REPLACE FUNCTION merger(text,anyarray) RETURNS text AS '
      SELECT COALESCE($1 || $2[1] || $2[2],$1,$2[2],NULL)

   -- join(val,cols) aggregate
      sfunc = merger,
      basetype = anyarray,
      stype = text
The first bit is basically what you expect SUM() to do if you pass it text instead of numbers. (You can even call it SUM if you like by changing the name from concatenate).

After running this snippet on a Postgresql database (with functions enabled and assuming that the user has appropriate CREATE privalages), you can then do things like this:

CREATE TEMPORARY TABLE article(words text);
INSERT INTO article VALUES ('hello');
INSERT INTO article VALUES ('to');
INSERT INTO article VALUES ('you');
SELECT concatenate(words || ' ') FROM article;
The final SELECT will return the string 'hello to you '

I find this useful in the case that you have two tables with a 1:N relationship (say, newspapers and articles) and you want to make up the text in a view of the newspaper from the articles.

Now what we really want is something to do a Perl-style join, so that one could run:
SELECT merge(ARRAY[' ', words]) FROM article;
and get 'hello to you' without the space at the end. That's what the second aggregate does, although it should be noted that it's less efficient because you need to create an array for every element, just to pass the join text, which should really be stored in the initial condition somehow . But Postgresql doesn't support that as far as I can tell; someone please correct me if I'm wrong.

Last edited by rjlee; 07-13-2005 at 05:12 PM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
PERL: concatenation of variables issue randomx Programming 2 01-16-2005 05:52 PM
Searching for software with scanning, text/graphic editing, etc. functions satimis Linux - Software 0 11-06-2004 07:07 PM
merge two files without repeating text pukas Linux - General 0 02-18-2004 08:22 PM
GUI test tool to perform widget functions via a text file liguorir Linux - Software 0 01-05-2004 03:30 PM
string concatenation in AWK xanthium Programming 1 04-22-2002 05:41 AM > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 01:28 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration