Error 42704 - Mailing list pgsql-sql

From Kim Bisgaard
Subject Error 42704
Date
Msg-id 5072F386.3020802@alleroedderne.adsl.dk
Whole thread Raw
List pgsql-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:

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 problem will be received with 
pleasure.

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

Thanks in advance!

Regards,
Kim




pgsql-sql by date:

Previous
From: Gavin Flower
Date:
Subject: Re: checking the gaps in intervals
Next
From: Sebastien FLAESCH
Date:
Subject: Database object names and libpq in UTF-8 locale on Windows