Need help with a function from hell.. - Mailing list pgsql-general

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';