Re: Aggregate not working as expected

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