Re: Need help with a function from hell.. - Mailing list pgsql-general
From | Chris Kratz |
---|---|
Subject | Re: Need help with a function from hell.. |
Date | |
Msg-id | 200610040859.23321.chris.kratz@vistashare.com Whole thread Raw |
In response to | Need help with a function from hell.. (arsi@aranzo.netg.se) |
List | pgsql-general |
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
pgsql-general by date: