accessing anyarray elements - Mailing list pgsql-general

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