From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Craig Barnes <cjbarnes18(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Aggregate not working as expected |
Date: | 2011-10-13 10:04:24 |
Message-ID: | CAA-aLv7SPFEQj+4Ayq32YMxQUHJN2j2V9Dn2gxQ-NGffE7zS4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 13 October 2011 10:06, Craig Barnes <cjbarnes18(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Barnes | 2011-10-13 11:16:49 | Re: Aggregate not working as expected |
Previous Message | Craig Barnes | 2011-10-13 09:06:52 | Aggregate not working as expected |