Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with custom aggregates and record pseudo-type

Problem with custom aggregates and record pseudo-type

From
Maxim Boguk
Date:
I created special custom aggregate function to append arrays defined as:
CREATE AGGREGATE array_accum (anyarray)
(
    sfunc = array_cat,
    stype = anyarray,
    initcond = '{}'
);


On arrays of common types it work without any problems:
SELECT array_accum(i) from (values (ARRAY[1,2]), (ARRAY[3,4])) as t(i);
 array_accum
-------------
 {1,2,3,4}
(1 row)


However once I try use it with record[] type I get an error:
SELECT array_accum(i) from (values (ARRAY[row(1,2),row(2,3)]), (ARRAY[row(1,2),row(2,3)])) as t(i);
ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with element types record[] and record are not compatible for concatenation.


The base function of the aggregate:
array_cat work with record[] without any complains:
SELECT array_cat(ARRAY[row(1,2),row(2,3)], ARRAY[row(1,2),row(2,3)]);
             array_cat
-----------------------------------
 {"(1,2)","(2,3)","(1,2)","(2,3)"}


What I doing wrong? Or how to create correct version of such aggregate function?


--
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все.

Re: Problem with custom aggregates and record pseudo-type

From
Tom Lane
Date:
Maxim Boguk <maxim.boguk@gmail.com> writes:
> I created special custom aggregate function to append arrays defined as:
> CREATE AGGREGATE array_accum (anyarray)
> (
>     sfunc = array_cat,
>     stype = anyarray,
>     initcond = '{}'
> );


> On arrays of common types it work without any problems:
> SELECT array_accum(i) from (values (ARRAY[1,2]), (ARRAY[3,4])) as t(i);
>  array_accum
> -------------
>  {1,2,3,4}
> (1 row)


> However once I try use it with record[] type I get an error:
> SELECT array_accum(i) from (values (ARRAY[row(1,2),row(2,3)]),
> (ARRAY[row(1,2),row(2,3)])) as t(i);
> ERROR:  cannot concatenate incompatible arrays

Hm ... it looks like this case can be made to work with a simple adjustment
to getTypeIOData, but in the meantime you might be able to get away with
leaving the initial value as NULL (ie, leave off the initcond clause).
The only behavioral difference would be that you'd get NULL not an empty
array for zero rows of input.

            regards, tom lane

Re: Problem with custom aggregates and record pseudo-type

From
Maxim Boguk
Date:


On Fri, Dec 2, 2011 at 3:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Maxim Boguk <maxim.boguk@gmail.com> writes:
> I created special custom aggregate function to append arrays defined as:
> CREATE AGGREGATE array_accum (anyarray)
> (
>     sfunc = array_cat,
>     stype = anyarray,
>     initcond = '{}'
> );


> On arrays of common types it work without any problems:
> SELECT array_accum(i) from (values (ARRAY[1,2]), (ARRAY[3,4])) as t(i);
>  array_accum
> -------------
>  {1,2,3,4}
> (1 row)


> However once I try use it with record[] type I get an error:
> SELECT array_accum(i) from (values (ARRAY[row(1,2),row(2,3)]),
> (ARRAY[row(1,2),row(2,3)])) as t(i);
> ERROR:  cannot concatenate incompatible arrays

Hm ... it looks like this case can be made to work with a simple adjustment
to getTypeIOData, but in the meantime you might be able to get away with
leaving the initial value as NULL (ie, leave off the initcond clause).
The only behavioral difference would be that you'd get NULL not an empty
array for zero rows of input.

                       regards, tom lane

Thank you very much for an idea, you suggestiong work excellent as usual.
And again thank you for commiting a fix.

Kind Regards,
Maksym