Thread: accessing anyarray elements

I'd like to be able to access individual elements of anyarray,
treating them as type anyelement to take advantage of the
polymorphism. Using pg_stats.histogram_bounds as a convenient example
of an anyelement array, here's an example of the issue I'm running into.

test_anyarray=# select
version
();

      version  


-----------------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.4beta2 on i386-apple-darwin9.6.0, compiled by GCC i686-
apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5490), 32-bit
(1 row)


histogram_bounds for for pg_catalog.pg_type.typelen

test_anyarray=# SELECT histogram_bounds FROM pg_stats WHERE
(schemaname,tablename,attname) = ('pg_catalog','pg_type','typlen');
  histogram_bounds
------------------
  {-2,-2,1,2,6,64}
(1 row)

checking to see if functions treat the histogram_bounds anyarray as an
array:

test_anyarray=# SELECT histogram_bounds, array_upper(histogram_bounds,
1) FROM pg_stats WHERE (schemaname,tablename,attname) =
('pg_catalog','pg_type','typlen');
  histogram_bounds | array_upper
------------------+-------------
  {-2,-2,1,2,6,64} |           6
(1 row)

Trying to access the first element of histogram_bounds:

test_anyarray=# SELECT histogram_bounds, histogram_bounds[1] FROM
pg_stats WHERE (schemaname,tablename,attname) =
('pg_catalog','pg_type','typlen');
ERROR:  cannot subscript type anyarray because it is not an array

Now, by casting through text to a specific array type, it works.

test_anyarray=# SELECT histogram_bounds,
(histogram_bounds::text::int[])[1] FROM pg_stats WHERE
(schemaname,tablename,attname) = ('pg_catalog','pg_type','typlen');
  histogram_bounds | histogram_bounds
------------------+------------------
  {-2,-2,1,2,6,64} |               -2
(1 row)

However, this casting defeats the purpose of using a polymorphic type.

It appears this issue has come up before:

<http://archives.postgresql.org/message-id/20070801020230.GL15602@alvh.no-ip.org
 >

and tangentially here:
<http://archives.postgresql.org/message-id/14653.1229215105@sss.pgh.pa.us
 >

In this particular case, I'm hoping to get at the histogram array to
look at the data distribution in a general way:

CREATE SCHEMA utility;
CREATE OR REPLACE FUNCTION
utility.histogram_bounds(in_histogram_bounds anyarray,
                          OUT bucket_index integer,
                          OUT lower_bound anyelement,
                          OUT upper_bound anyelement,
                          OUT width anyelement,
                          OUT cumulative_width anyelement)
RETURNS SETOF RECORD
STRICT
LANGUAGE plpgsql AS $body$
DECLARE
   v_idx INTEGER;
BEGIN
   cumulative_width := 0;
   bucket_index := 0;
   FOR v_idx IN 1..array_upper(in_histogram_bounds,1) LOOP
     lower_bound := upper_bound;
     upper_bound := in_histogram_bounds[v_idx];
     CONTINUE WHEN v_idx = 1;
     bucket_index := bucket_index + 1;
     width := upper_bound - lower_bound;
     cumulative_width := cumulative_width + width;
     RETURN NEXT;
   END LOOP;
   RETURN;
END
$body$;

Any ideas on how I might implement this? Would it require a change in
the backend?

Cheers,

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> I'd like to be able to access individual elements of anyarray,
> treating them as type anyelement to take advantage of the
> polymorphism. Using pg_stats.histogram_bounds as a convenient example
> of an anyelement array, here's an example of the issue I'm running into.

The problem with histogram_bounds is that there isn't any way to
infer an element type for it in the abstract, and thus also no way to
know what type anyelement is.  When looking at an individual row you can
know that it must have the type of the associated column and explain
that to the parser via an explicit cast, but there's pretty much no hope
of having that happen automagically.

There are a few other problems, like array columns --- the contents of
pg_statistic for them is actually an array of arrays, which simply
has not got a representation in our type system.

So you're pretty much out of luck.  I think the only meaningful thing
you can do with it in SQL is cast to text.

            regards, tom lane