LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
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.

Notices

Reply
 
Search this Thread
Old 07-13-2005, 04:10 PM   #1
rjlee
Senior Member
 
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,990

Rep: Reputation: 67
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:
Code:
   -- Function form of || operator
   CREATE OR REPLACE FUNCTION catenate(text,text) RETURNS text AS '
      SELECT COALESCE($1 || $2,$1,$2,NULL)
   ' LANGUAGE SQL;
	
   -- 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)
   ' LANGUAGE SQL;

   -- join(val,cols) aggregate
   CREATE AGGREGATE merge (
      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:

Code:
CREATE TEMPORARY TABLE article(words text);
INSERT INTO article VALUES ('hello');
INSERT INTO article VALUES ('to');
INSERT INTO article VALUES ('you');
INSERT INTO article VALUES (NULL);
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:
Code:
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 04:12 PM.
 
  


Reply


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 04:52 PM
Searching for software with scanning, text/graphic editing, etc. functions satimis Linux - Software 0 11-06-2004 06:07 PM
merge two files without repeating text pukas Linux - General 0 02-18-2004 07:22 PM
GUI test tool to perform widget functions via a text file liguorir Linux - Software 0 01-05-2004 02:30 PM
string concatenation in AWK xanthium Programming 1 04-22-2002 04:41 AM


All times are GMT -5. The time now is 10:10 PM.

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