Re: Need help with a function from hell..

Hello Archie,

We approach the problem slightly differently then others. Given an aggregate
function comma_list which simply creates a comma seperated list, we use
distinct to remove duplicates.

test=# select comma_list(col) from test;
comma_list
------------
a, b, a, c
(1 row)

test=# select comma_list(distinct col) from test;
comma_list
------------
a, b, c
(1 row)

I've included our function definitions below.

hope that helps,

-Chris

CREATE OR REPLACE FUNCTION list_add(text, text)
RETURNS text AS
$BODY$
select
CASE WHEN $2 IS NULL OR $2 ='' THEN $1
WHEN $1 IS NULL or $1 = '' THEN $2
ELSE $1 || ', ' || $2
END;
$BODY$
LANGUAGE 'sql' VOLATILE;

CREATE OR REPLACE FUNCTION list_fin(text)
RETURNS text AS
$BODY$
SELECT CASE WHEN $1=text('') THEN NULL
ELSE $1 END
$BODY$
LANGUAGE 'sql' VOLATILE;

CREATE AGGREGATE comma_list(
BASETYPE=text,
SFUNC=list_add,
STYPE=text,
FINALFUNC=list_fin
);

On Tuesday 03 October 2006 03:26 pm, arsi(at)aranzo(dot)netg(dot)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

--
Chris Kratz