Re: [GENERAL] Howto convert arrays 2 query results

On 10-Jun-99 Jeroen Schaap wrote:
>
> Herouth, thanks a lot for your answer. But I'm afraid I was rather unclear.

[...]

> 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.

Your DB is broken by design:

tables 'agents' and 'solution' are o.k., but ...

create table medium (ID int, Name text);
insert into medium (1,'Strong case');
insert into medium (2,'Nearly tasteless');

create table relation (S_ID int, M_ID int);
insert into relation (2,1);
insert into relation (4,1);
insert into relation (1,2);
insert into relation (3,2);

will normalize your DB, so you don't need an array.

Gerald