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
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 |