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@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

--
Chris Kratz