Re: Aggregate not working as expected

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