Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group