Herouth, thanks a lot for your answer. But I'm afraid I was rather unclear.
On 09-Jun-99 Herouth Maoz wrote:
> At 10:13 +0300 on 09/06/1999, Jeroen Schaap wrote:
>> Do you know of any way to generally convert arrays into query results?
>>
>> I know it is better to implement arrays as tables, but that results in
>> unreadable tables (with 10 rows with id=1, 15 with id=2, 2 with id=3 ad
>> infundum...).
>>
>> So is there any way to convert an array into a table? Should I
>> write a function or a C-function?
>
> It's not entirely clear what you want. The reason to keep arrays together
I'm sorry for being unclear about my problem.
> in a separate table is organizational. The way you want to present the
> arrays shoud not affect the way they are organized.
I'm not bothered by the representation, but by the way I can build queries.
> If it bothers you that a query returns something like
[nice solution to misstated problem snipped]
OK, I will try to explain my problem using an example. The easiest one is the
chemical solution database. Of course this example has been simplified.
create table agents ( ID int, Name text);
insert into agents (1, 'salt');
insert into agents (2, 'sugar');
create table solution (ID int, agent_ID int, concentration float);
insert into solution(1,1,1.5);
insert into solution(2,1,20.5);
insert into solution(3,2,1.5);
insert into solution(4,2,20.5);
create table medium (ID int, Name text, solutions int[]);
insert into solution(1,'Strong case','{2,4}');
insert into solution(2,'Nearly tasteless','{1,3}');
Now I want all the names of the agents that are in the 'nearly tasteless'
medium, as well as the concentrations.
An efficient way to write such a query would be:
select M.ID, M.Name, S.concentration, A.Name from agent A, solution S,
medium M where S.ID in (select M.solutions where M.ID=2) and
A.ID=S.agent_ID;
But this is impossible because the resulting query from the select
M.solutions is an array. I would like a function to convert this array to a
query result.
Thank you for your attention,
Jeroen
---
Jeroen Schaap.............I was dreaming of guitarnotes that would irritate
Homepage: <http://rulffh.medfac.leidenuniv.nl>..| ^|^ |...an executive kind
Keywords: Guitars, Linux, Mac and SCN...........\_`-'_/..............of guy
Tel: (0)71-5276811................................| |...........Frank Zappa