Problem with custom aggregates and record pseudo-type
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/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.
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
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 arraysHm ... 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