Re: Oddities with ANYARRAY

Decibel! wrote:
> David Fetter and I were just looking at something on IRC...
>
> decibel=# select most_common_vals[1] from pg_stats where tablename='pg_depend' and attname='classid';
> ERROR: cannot subscript type anyarray because it is not an array
> decibel=# select most_common_freqs[1] from pg_stats where tablename='pg_depend' and attname='classid';
> most_common_freqs
> -------------------
> 0.566
> (1 row)

The difference is that most_common_freqs is a real[], which means it
behaves as a regular array (subscript etc). most_common_vals, on the
other hand, is a quite hackish thing and very unique -- it's the only
column in all the database that has type anyarray.

The reason for most_common_vals being anyarray is that it must be able
to hold an array of any type at all; but the problem is that it is quite
difficult to make it work anywhere else, because normally anyarray types
are resolved to some specific array type early in the life of a query.
In this case it cannot.

In fact, standalone mode has a very particular hack to allow anyarray to
be used as a type in a table, which is there precisely (and only) for
allowing pg_statistic to get created.

> decibel=# CREATE OR REPLACE FUNCTION array_to_set(anyarray) RETURNS SETOF anyelement LANGUAGE SQL AS $$
> SELECT $1[i] from generate_series(array_lower($1, 1), array_upper($1, 1)) i
> $$;
> CREATE FUNCTION
> decibel=# select array_to_set(most_common_vals) from pg_stats where tablename='pg_depend' and attname='classid';
> ERROR: argument declared "anyarray" is not an array but type anyarray

Yeah, that error message is weird.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.