On 13 October 2011 11:04, Thom Brown <thom@linux.com> wrote:
> On 13 October 2011 10:06, Craig Barnes <cjbarnes18@gmail.com> wrote:
>> Hello,
>>
>> I have created a text concatenation function
>>
>> CREATE OR REPLACE FUNCTION commacat(acc text, instr text) RETURNS text
>> LANGUAGE plpgsql
>> AS $$
>> declare
>> x text;
>> BEGIN
>> x := trim(both from acc);
>> IF char_length(x) < 1 THEN
>> RETURN instr;
>> ELSE
>> RETURN instr || ', ' || x;
>> END IF;
>> END;
>> $$;
>>
>> Which when called works as expected.
>>
>> SELECT commacat(' ','z')
>>
>>> "z"
>>
>> I have created an aggregate which calls the function.
>>
>> CREATE AGGREGATE textcat_all (text)(
>> SFUNC = commacat,
>> STYPE = text,
>> INITCOND = ''
>> );
>>
>> But when called does not produce expected results
>>
>> begin;
>> create temporary table x (y text);
>> insert into x values(' ');
>> insert into x values('abc');
>> insert into x values('def');
>> insert into x values('');
>> insert into x values('z');
>>
>>> Query returned successfully: 1 row affected, 15 ms execution time.
>>
>> select textcat_all(y) from x;
>>
>>> "z, , def, abc"
>>
>>
>> I cannot find what it is that I am doing wrong.
>
> If you're wondering why you've got a blank entry in the output, the
> problem is that you are checking to see whether your accumulated
> aggregate is empty, but not your input.
>
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
Thanks Thom,
I understand what this snippet was intended to do now.
My result is.
CREATE OR REPLACE FUNCTION commacat(acc text, instr text) RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
IF acc IS NULL OR trim(both from acc) = '' THEN
RETURN instr;
ELSIF instr IS NULL OR trim(both ' ' from instr) = '' THEN
RETURN acc;
ELSE
RETURN acc || ', ' || instr;
END IF;
END;
$$;
Thanks Again
Craig
