Re: could not find array type for data type character varying[]

On Wed, Oct 28, 2009 at 11:17 AM, Viktor Rosenfeld
<listuser36(at)googlemail(dot)com> wrote:
> Hi,
>
> I'm trying to aggregate a list of table attributes into an array.
>
> The actual code looks something like this:
>
>  SELECT
>    node_ref AS id,
>    array_agg(DISTINCT ARRAY[namespace, name, value]) as annotations
>  ...
>  GROUP BY id;
>
> I guess the minimal example that reproduces the error is:
>
>  annis=> select array_agg(array['a'::varchar, 'b', 'c']);
>  ERROR:  could not find array type for data type character varying[]
>
> Why doesn't this work?

There are no arrays of arrays. There are however multi dimension
arrays and arrays of composite types (which may contain arrays).
Pick your poison. Also, prefer array() to array_agg if you are not
truly aggregating (hard to tell in this query).

your problem:
postgres=# select array(select current_schemas(true));
ERROR: could not find array type for datatype name[]

possible fix:
postgres=# select array(select row(current_schemas(true)));
?column?
-----------------------------------------------
{"(\"{pg_catalog,public,dblink,pgcrypto}\")"}

another way:
postgres=# select (v[1]).n[1] from (select array(select
row(current_schemas(true))::a) as v) q;
n
------------
pg_catalog
(1 row)

head spinning yet? :-)

merlin