Thread: Adjusting a mode function for the deprecation of anyarray
We have had and used this basic dumb useful aggregate in PG 13 and earlier:
CREATE OR REPLACE FUNCTION _final_mode(anyarray)
RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$BODY$
LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE mode(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_mode,
INITCOND='{}'
);
RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$BODY$
LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE mode(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_mode,
INITCOND='{}'
);
I can't seem to figure out how to adjust the mode(anyelement) bit now for PG 14 and the loss of anyarray. Changing STYPE to anyelement yields "function array_append(anyelement, anyelement) does not exist" and changing it to anycompatiblearray yields:
ERROR: cannot determine transition data type
DETAIL: A result of type anycompatiblearray requires at least one input of type anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange, or anycompatiblemultirange.
--
DETAIL: A result of type anycompatiblearray requires at least one input of type anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange, or anycompatiblemultirange.
What's the obvious thing I am missing here? Thank you.
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Hi Wells,
This is a pgAdmin mailing list. You will get better support on PostgreSQL mailing list - pgsql-admin@postgresql.org.
On Sun, Jan 15, 2023 at 2:45 AM Wells Oliver <wells.oliver@gmail.com> wrote:
We have had and used this basic dumb useful aggregate in PG 13 and earlier:CREATE OR REPLACE FUNCTION _final_mode(anyarray)
RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$BODY$
LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE mode(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_mode,
INITCOND='{}'
);I can't seem to figure out how to adjust the mode(anyelement) bit now for PG 14 and the loss of anyarray. Changing STYPE to anyelement yields "function array_append(anyelement, anyelement) does not exist" and changing it to anycompatiblearray yields:ERROR: cannot determine transition data type
DETAIL: A result of type anycompatiblearray requires at least one input of type anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange, or anycompatiblemultirange.What's the obvious thing I am missing here? Thank you.--Wells Oliver
wells.oliver@gmail.com
Thanks,
Aditya Toshniwal
pgAdmin Hacker | Software Architect | edbpostgres.com
"Don't Complain about Heat, Plant a TREE"