From: Kim Bisgaard <kim+pg@alleroedderne.adsl.dk>
To: pgsql-sql@postgresql.org
Subject: Error 42704
Date: Mon, 08 Oct 2012 17:38:46 +0200
Message-ID: <5072F386.3020802@alleroedderne.adsl.dk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kim+pg@alleroedderne.adsl.dk
Subject: Re: Error 42704
In-Reply-To: <5072F386.3020802@alleroedderne.adsl.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox