Обсуждение: Aggregate not working as expected

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.

Version string    PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit

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

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