Thread: Using unnest

Hi,

I was trying to use information from the pg_stats view, when I remembered that 8.4 now has the cool
unnest function.

However I can't seem to get this to work with a column defined as anyarray.

So my query is:

select histogram_bounds
from pg_stats
where tablename = 'my_table'
and attname = 'col1';

Now I would like to get the elements of the histogram_bounds column as a set, and thought that
unnest would help me here, but the following:

select unnest(histogram_bounds)
from pg_stats
where tablename = 'my_table'
and attname = 'col1';

gives me the error

argument declared "anyarray" is not an array but type anyarray

I'm pretty sure I'm overlooking something obvious with regards to the unnest syntax, but what?

Thanks
Thomas

On Sat, 2009-07-25 at 22:24 +0200, Thomas Kellerer wrote:
> I was trying to use information from the pg_stats view, when I remembered that 8.4 now has the cool
> unnest function.
>
> However I can't seem to get this to work with a column defined as anyarray.

It's generally hard to work with values of type anyarray. You have to
cast them to text and then to a normal array type.

For example:

  select unnest(histogram_bounds::text::oid[]) from pg_stats where
         tablename='pg_amop' and attname='amopopr';

Regards,
    Jeff Davis

Jeff Davis wrote on 25.07.2009 22:44:
> It's generally hard to work with values of type anyarray. You have to
> cast them to text and then to a normal array type.
>
> For example:
>
>   select unnest(histogram_bounds::text::oid[]) from pg_stats where
>          tablename='pg_amop' and attname='amopopr';

Great, thanks

I tried casting the column to text[] (because it contains elements of type text) but I didn't think
of doing a two way cast.

Do I understand this correctly that by casting it first to text, I effectively create a new array
the same way I create one, when I supply a literal like '{1,2,3}'::text[]

Thomas

On Sat, 2009-07-25 at 22:54 +0200, Thomas Kellerer wrote:
> Do I understand this correctly that by casting it first to text, I effectively create a new array
> the same way I create one, when I supply a literal like '{1,2,3}'::text[]

Similar, but not quite the same. '{1,2,3}'::text[] is actually
constructing from the cstring type using the type input function.
Cstring is not a normal type, it is what things are before they have a
normal type.

So, my strategy will only work if the array type you're trying to cast
to has a cast from text.

There aren't separate input functions for each array type, so it's hard
to make this work without depending on a cast from text. Perhaps someone
else has a better idea, though.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> So, my strategy will only work if the array type you're trying to cast
> to has a cast from text.

As of (IIRC) 8.3, every type does have a cast from text.

            regards, tom lane