From: | Gerald Fiedler <gerald(at)interface-business(dot)de> |
---|---|
To: | Jeroen Schaap <jeroen(at)rulffh(dot)medfac(dot)leidenuniv(dot)nl> |
Cc: | pgsql-general(at)postgreSQL(dot)org, Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
Subject: | Re: [GENERAL] Howto convert arrays 2 query results |
Date: | 1999-06-10 11:06:44 |
Message-ID: | XFMail.990610130644.gerald@interface-business.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeroen Schaap | 1999-06-10 11:26:01 | Re: [GENERAL] Howto convert arrays 2 query results |
Previous Message | Jelle Ruttenberg | 1999-06-10 10:22:41 | Installing PostgreSQL6.4.2.: createdb and ipc/semctl-bug(?) |