Re: [GENERAL] Howto convert arrays 2 query results

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