Re: Need help with a function from hell..

Well, first off, this would be much easier in one of the other pl's such
as for perl or ruby.  Using plpgsql, I would suggest using more of the
string function split_part since you know the delimiters the string can
split on, using str_pos just to verify that there is say a '/' in a part
of the string.  Or, you could use the substring function (regex) to pull
out portions to process (this is where plperl and plruby would work much
better).  Then just loop through, continually splitting the string down
into it's component parts, maybe placing the component parts into arrays
to preserve the relationships, then do your checks for equality against
the "keys" that you've split up and rebuild the whole string from the
ground up.

arsi@aranzo.netg.se wrote:
>
> 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';
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match


--
erik jones <erik@myemma.com>
software development
emma(r)