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@xxxxxxxxxxxxxx
>
and tangentially here:
<http://archives.postgresql.org/message-id/14653.1229215105@xxxxxxxxxxxxx
>
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
