Need help with a function from hell..

[arsi@xxxxxxxxxxxxxx]

Hi all,

I have a small coding problem where my function is becoming, well, too 
ugly for comfort. I haven't finished it but you will get picture below.

First a small description of the purpose. I have an aggregate function 
that takes a string and simply concatenates that string to the previous 
(internal state) value of the aggregate, example:

"Hello:World" || ", " || "World:Hello" --> "Hello:World, World:Hello"

My problem is that I sometimes get the same value before the colon 
sign and in those cases I should not add the whole string to the previous 
value of the aggregate but extract the value that is behind the colon and 
add it to already existing part which matched the value before the colon 
but with a slash as a delimiter, example:

Internal state: "Hello:World, World:Hello" 
New value: "Hello:Dolly"
After function is run: "Hello:World/Dolly, World:Hello"

So what I am doing is a lot of strpos() and substr() functions (I have 
previously asked for the speed of the substr() function) but it is 
beginning to look really alwful.

It seems very odd that there doesn't exist something else like what I need 
but I haven't found anything, although I admit I might not understand all 
aspects of the PostGreSQL database and what I can do with the SQL in 
connection to it.

Below you will find my unfinished function, but it will show you what I 
mean when I say ugly..

Any help is appreciated.

Thanks in advance,

Archie


CREATE FUNCTION rarity_concat(text, text)
  RETURNS text
  AS
    'DECLARE
       colon_pos integer;
       set_str text;
       rarity_str text;
       set_exist_pos integer;
       rarity_exist_str_middle text;
       rarity_exist_str_end text;
     BEGIN
       colon_pos := strpos($2, ':');
       set_str := substr($2, 1, colon_pos);
       set_exist_pos := strpos($1, set_str);
       IF set_exist_pos > 0 THEN
         rarity_str := substr($2, colon_pos + 2);
         rarity_exist_str_start := substr($1, 1, set_exist_pos - 1);
         comma_pos :=
       ELSE
          RETURN $1 || \', \' || $2;
       END IF;
     END'
  LANGUAGE 'plpgsql';