Thread: Error 42704 - does mean what?

[Cross post from -SQL]

Hi,

I am trying to model a macro system where I have simple things, and more complex thing consisting of simple things. To
dothat I have  
"invented" this table definition:

CREATE TABLE params
(
   param_id serial NOT NULL,
   name text NOT NULL,
   unit text,
   real_param_id integer[],
   CONSTRAINT params_pkey PRIMARY KEY (param_id),
   CONSTRAINT params_name_key UNIQUE (name)
);

with a complex and 2 simple things:
INSERT INTO params VALUES (1, 'a', NULL, '{1,2}');
INSERT INTO params VALUES (2, 'a1', '1', NULL);
INSERT INTO params VALUES (3, 'a2', '2', NULL);

So I want to get a listing of things, both simple and complex
col1  col2
-----+------------------------
a1    {{a1, 1}}
a2    {{a2, 2}}
a      {{a1, 1},{a2,2}}

with this SQL:
  select name, array[array[name::text,unit::text]]::text[][]
  from params
  where real_param_id is null
union
  select name, array(select cast('{"'||a.name||'","'||a.unit||'"}' as text[])
                     from params a,
                          (select c.param_id, unnest(real_param_id)
                           from params c
                           where c.param_id=b.param_id) as j
                     where a.param_id = j.unnest)
  from params b
  where b.real_param_id is not null
order by name

But I am getting this error which I do not find very informative, as I know i can have arrays of text and arrays of
those,so what is up? 

ERROR: could not find array type for data type text[]
SQL state: 42704

Suggestions as to what to do to circumvent this error, and also to maybe more elegant ways to solve the fundamental
problemwill be received  
with pleasure.

This is tested on both PostgreSQL 9.2.1 and a 9.1.*

Thanks in advance!

Regards,
Kim

On 10/15/2012 03:37 PM, Kim Bisgaard wrote:
> [Cross post from -SQL]
>
> Hi,
>
> I am trying to model a macro system where I have simple things, and more
> complex thing consisting of simple things. To do that I have "invented"
> this table definition:

Didn't I recently see something like this on Stack Overflow? I can't
seem to find the question in searches but I remember that description.

> ERROR: could not find array type for data type text[]
> SQL state: 42704

According to:

http://www.postgresql.org/docs/current/static/errcodes-appendix.html

42704 is "undefined_object".


What it really means is that a multi-dimensional array is not just an
array of arrays.

regress=# SELECT pg_typeof(ARRAY[['a','b'],['c','d']]);
  pg_typeof
-----------
  text[]
(1 row)

regress=# WITH arr(x) AS (VALUES (ARRAY['a','b']), (ARRAY['c','d']))
SELECT array_agg(x) FROM arr;
ERROR:  could not find array type for data type text[]



--
Craig Ringer