Aggregate not working as expected

From: Craig Barnes <cjbarnes18(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Aggregate not working as expected
Date: 2011-10-13 09:06:52
Message-ID: CAH3ft_WeOUjBsfu94kp+1BxoOja_jeddLm4ZM4h5fsfAv1LGiA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thom Brown 2011-10-13 10:04:24 Re: Aggregate not working as expected
Previous Message Aleksej Trofimov 2011-10-13 08:26:36 Re: Query planner and variables with constraint_exclusion