Thread: Oddities with ANYARRAY
David Fetter and I were just looking at something on IRC...
decibel=3D# select most_common_vals[1] from pg_stats where tablename=3D'pg_=
depend' and attname=3D'classid';
ERROR: cannot subscript type anyarray because it is not an array
decibel=3D# select most_common_freqs[1] from pg_stats where tablename=3D'pg=
_depend' and attname=3D'classid';
most_common_freqs=20
-------------------
0.566
(1 row)
ISTM you'd want to be able to reference an individual element of an
ANYARRAY... but this gets even more odd...
decibel=3D# 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=3D# select array_to_set(most_common_vals) from pg_stats where table=
name=3D'pg_depend' and attname=3D'classid';
ERROR: argument declared "anyarray" is not an array but type anyarray
I expected that not to work, but the error is somewhat interesting...
I didn't expect the following to work:
decibel=3D# select array_to_set(most_common_freqs) from pg_stats where tabl=
ename=3D'pg_depend' and attname=3D'classid';
array_to_set=20
--------------
0.566
0.235667
0.126333
0.0343333
0.02
0.0163333
0.000666667
0.000666667
(8 rows)
decibel=3D#=20
ISTM you should be able to call an anyarray function with an anyarray,
and that you should be able to reference individual elements of an
anyarray...
--=20
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Decibel! <decibel@decibel.org> writes:
> ISTM you'd want to be able to reference an individual element of an
> ANYARRAY...
And what type would the result have?
pg_statistic is definitely pushing the boundaries of the type system
by having an anyarray column. We don't allow that in normal user
tables...
regards, tom lane
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.
"Decibel!" <decibel@decibel.org> writes: > 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) > > ISTM you'd want to be able to reference an individual element of an > ANYARRAY... but this gets even more odd... And what type would the result be? But this is an odd coincidence as I was just looking at this myself to do those histogram charts I was talking about. The solution I was going to propose was to allow casting from anyarray to a normal array, then allow subscripting the normal array. I would be fine requiring the cast to be to the correct array type with a run-time error if the type doesn't match. Or it could use the VIAIO cast which would work as long as the input format matched. So you could always cast to text[] even if it was an integer[] or something else originally. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Jul 31, 2007, at 11:55 PM, Gregory Stark wrote: > "Decibel!" <decibel@decibel.org> writes: > >> 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) >> >> ISTM you'd want to be able to reference an individual element of an >> ANYARRAY... but this gets even more odd... > > And what type would the result be? ANYELEMENT? I know that'd still have to be casted to something normal eventually; do we have support for that? > But this is an odd coincidence as I was just looking at this myself > to do > those histogram charts I was talking about. The solution I was > going to > propose was to allow casting from anyarray to a normal array, then > allow > subscripting the normal array. > > I would be fine requiring the cast to be to the correct array type > with a > run-time error if the type doesn't match. Or it could use the VIAIO > cast which > would work as long as the input format matched. So you could always > cast to > text[] even if it was an integer[] or something else originally. I'd expected that the 'ANY' types had additional information somewhere that told them what the original data type actually was, but I guess that's not the case. Maybe it'd be worth adding? -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
"Decibel!" <decibel@decibel.org> writes: > On Jul 31, 2007, at 11:55 PM, Gregory Stark wrote: >> >> And what type would the result be? > > ANYELEMENT? I know that'd still have to be casted to something normal > eventually; do we have support for that? There isn't really any such thing. There isn't really any such thing as anyarray either, the actual arrays are normal arrays of a real data type. anyarray and anyelement are things the parser and labels things it doesn't know better. Normally that's just parameters of polymorphic functions since you can't define columns of type anyarray normally. pg_statistic is a magic exception. > I'd expected that the 'ANY' types had additional information somewhere that told > them what the original data type actually was, but I guess that's not the case. > Maybe it'd be worth adding? Well arrays do. That's the only reason we can output the arrays from pg_statistic. So we could cast an anyarray to an array of a specific data type. The parser would be able to make sense of (histogram_bounds::text[])[1] since it's obviously a text. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Thu, Aug 02, 2007 at 12:44:02AM +0100, Gregory Stark wrote: >=20 > "Decibel!" <decibel@decibel.org> writes: >=20 > > On Jul 31, 2007, at 11:55 PM, Gregory Stark wrote: > >> > >> And what type would the result be? > > > > ANYELEMENT? I know that'd still have to be casted to something normal > > eventually; do we have support for that? >=20 > There isn't really any such thing. There isn't really any such thing as > anyarray either, the actual arrays are normal arrays of a real data type. >=20 > anyarray and anyelement are things the parser and labels things it doesn't > know better. Normally that's just parameters of polymorphic functions sin= ce > you can't define columns of type anyarray normally. pg_statistic is a mag= ic > exception. >=20 > > I'd expected that the 'ANY' types had additional information somewhere = that told > > them what the original data type actually was, but I guess that's not = the case. > > Maybe it'd be worth adding? >=20 > Well arrays do. That's the only reason we can output the arrays from > pg_statistic. So we could cast an anyarray to an array of a specific data > type. The parser would be able to make sense of (histogram_bounds::text[]= )[1] > since it's obviously a text. So is ANYARRAY actually a 'real' type in the sense that it remembers the data type that was passed into it? Or is it more like a label like you said above? How horrible would it be to add the ability for an anyarray/anyelement to know what the original data type was? There's certainly times when having that ability would be extremely useful; right now people just fudge it by using text instead. --=20 Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)