Thread: Howto convert arrays 2 query results
Dear psql'ers, 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? Thanks a lot, 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
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
in a separate table is organizational. The way you want to present the
arrays shoud not affect the way they are organized.
If it bothers you that a query returns something like
id person child
=== ======= =======
1 Susan Tom
1 Susan Merry
1 Susan Donna
2 George Ben
2 George Peggy
3 Morris Elias
And you want it to show something like:
Person Children
====== ========
Susan Tom, Merry, Donna
George Ben, Peggy
Morris Elias
What you do is write it this way in the frontend. It depends on your
favourite frontend language, but the general algorithm should be something
along the lines of:
last_id = 0;
while ( still_more_tuples )
get_next_tuple;
if ( tuple.id = last_id )
print( "," + tuple.child )
else
print( <newline> + tuple.person + <tab> + tuple.child )
end if
last_id = tuple.id;
end while
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
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
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
On 10-Jun-99 Gerald Fiedler wrote: > 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. Yes, it sure will. But only, I don't want to break up the arrays because the tables become unreadible when I have to add 15 solutions to a particular medium. And I try to keep the tables editable by hand. So I thought it would be possible to make a function that converts an array to a query result. But I really don't know whether this could be practically possible. I'm not too effluent in c, so I wanted to ask before I start to write code and duplicate something that already exists.... Thanks a lot, 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