Thread: Re: [GENERAL] Selecting from arrays
On Fri, 7 May 1999, Carlos Peralta Ramirez wrote:
>
>
> Simon Drabble wrote:
>
> > results table:
> >
> > date loc temperature (int4[])
> > 12/01/98 4th & Main {12, 14, 14, 15, 17, 19, 25, ...}
> > 12/04/98 Station 4 {12, 13, 14, 666, 18, 20, 24, ...}
> >
> > I want to select only those rows where one of the recorded temps is equal to ,
> > say, 17 , and also to select a range (rows where temp > 20, or where temp <
> > 10). I might also want to select rows where the temp could not be measured
> > (indicated by 666) at a certain period.
> >
>
> How you can do it ????
> You create a function or ..????
> You can send me some example, please ????
> I try to make a function, for manegement the arrays , Me right ?
>
> >
I'd rather not have to code a function for something which, it seems to
me, should be part of the installation. The application is eventually
intended for several platforms all of which might be running different
DBMS's, so I'd like to know if there's a common SQL way to do this and if
so if Postgres supports it.
Simon.
--
"When a German dwarf dances with the butcher's son.." -- Tom Waits
Simon Drabble Somewhere in cyberspace
simond@foxlink.net
There are some functions in contrib/ that handle some of these cases.
> > > date loc temperature (int4[])
> > > 12/01/98 4th & Main {12, 14, 14, 15, 17, 19, 25, ...}
> > > 12/04/98 Station 4 {12, 13, 14, 666, 18, 20, 24, ...}
> > >
> > > I want to select only those rows where one of the recorded temps is equal to ,
> > > say, 17 , and also to select a range (rows where temp > 20, or where temp <
> > > 10). I might also want to select rows where the temp could not be measured
> > > (indicated by 666) at a certain period.
> > >
> >
> > How you can do it ????
> > You create a function or ..????
> > You can send me some example, please ????
> > I try to make a function, for manegement the arrays , Me right ?
> >
> > >
>
> I'd rather not have to code a function for something which, it seems to
> me, should be part of the installation. The application is eventually
> intended for several platforms all of which might be running different
> DBMS's, so I'd like to know if there's a common SQL way to do this and if
> so if Postgres supports it.
>
> Simon.
>
> --
> "When a German dwarf dances with the butcher's son.." -- Tom Waits
>
> Simon Drabble Somewhere in cyberspace
> simond@foxlink.net
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
On Sat, 8 May 1999, Chris Bitmead wrote:
>
> There are some functions in contrib/ that handle some of these cases.
Thanks for the pointer.
I was kinda hoping to do it without resort to (external) functions,
since I cannot guarantee that Postgres will be the target DBMS and I also
cannot get array_iterator to compile :(
On the subject of contribs, what sort of licence are these distributed
under? I can't find any documentation for them except for the main
COPYRIGHT file in the top-level postgres directory. Does this mean the
contribs are covered by the same terms, so I can distribute the
array_iterator code with my app provided I include the copyright notice?
Simon.
>
> > > > date loc temperature (int4[])
> > > > 12/01/98 4th & Main {12, 14, 14, 15, 17, 19, 25, ...}
> > > > 12/04/98 Station 4 {12, 13, 14, 666, 18, 20, 24, ...}
> > > >
> > > > I want to select only those rows where one of the recorded temps is equal to ,
> > > > say, 17 , and also to select a range (rows where temp > 20, or where temp <
> > > > 10). I might also want to select rows where the temp could not be measured
> > > > (indicated by 666) at a certain period.
> > > >
> > >
> > > How you can do it ????
> > > You create a function or ..????
> > > You can send me some example, please ????
> > > I try to make a function, for manegement the arrays , Me right ?
> > >
> > > >
> >
> > I'd rather not have to code a function for something which, it seems to
> > me, should be part of the installation. The application is eventually
> > intended for several platforms all of which might be running different
> > DBMS's, so I'd like to know if there's a common SQL way to do this and if
> > so if Postgres supports it.
> >
> > Simon.
> >
> > --
> > "When a German dwarf dances with the butcher's son.." -- Tom Waits
> >
> > Simon Drabble Somewhere in cyberspace
> > simond@foxlink.net
>
> --
> Chris Bitmead
> http://www.bigfoot.com/~chris.bitmead
> mailto:chris.bitmead@bigfoot.com
>
>
--
"When a German dwarf dances with the butcher's son.." -- Tom Waits
Simon Drabble Somewhere in cyberspace
simond@foxlink.net
Simon Drabble wrote:
> I was kinda hoping to do it without resort to (external)
> functions, since I cannot guarantee that Postgres will be the
> target DBMS
Do other SQL databases even have arrays? I kinda assumed that they were
a Postgres thing in any case.
> COPYRIGHT file in the top-level postgres directory. Does this mean the
> contribs are covered by the same terms, so I can distribute the
> array_iterator code with my app provided I include the copyright notice?
Well, that is the assumption I would be working under.
>
> Simon.
>
> >
> > > > > date loc temperature (int4[])
> > > > > 12/01/98 4th & Main {12, 14, 14, 15, 17, 19, 25, ...}
> > > > > 12/04/98 Station 4 {12, 13, 14, 666, 18, 20, 24, ...}
> > > > >
> > > > > I want to select only those rows where one of the recorded temps is equal to ,
> > > > > say, 17 , and also to select a range (rows where temp > 20, or where temp <
> > > > > 10). I might also want to select rows where the temp could not be measured
> > > > > (indicated by 666) at a certain period.
> > > > >
> > > >
> > > > How you can do it ????
> > > > You create a function or ..????
> > > > You can send me some example, please ????
> > > > I try to make a function, for manegement the arrays , Me right ?
> > > >
> > > > >
> > >
> > > I'd rather not have to code a function for something which, it seems to
> > > me, should be part of the installation. The application is eventually
> > > intended for several platforms all of which might be running different
> > > DBMS's, so I'd like to know if there's a common SQL way to do this and if
> > > so if Postgres supports it.
> > >
> > > Simon.
> > >
> > > --
> > > "When a German dwarf dances with the butcher's son.." -- Tom Waits
> > >
> > > Simon Drabble Somewhere in cyberspace
> > > simond@foxlink.net
> >
> > --
> > Chris Bitmead
> > http://www.bigfoot.com/~chris.bitmead
> > mailto:chris.bitmead@bigfoot.com
> >
> >
>
> --
> "When a German dwarf dances with the butcher's son.." -- Tom Waits
>
> Simon Drabble Somewhere in cyberspace
> simond@foxlink.net
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
At 22:42 +0300 on 08/05/1999, Simon Drabble wrote: > I'd rather not have to code a function for something which, it seems to > me, should be part of the installation. The application is eventually > intended for several platforms all of which might be running different > DBMS's, so I'd like to know if there's a common SQL way to do this and if > so if Postgres supports it. If you want to do it in the SQL way, you won't use arrays. They are, at least in this use, contrary to the relational model. What you really should do is have two tables: One (results) should have: id (primary key) date location The other (temperatures) should have id (foreign key into the above table) temperature You enter each temperature into the second table along with the id of the record to which it belongs. And then your query is super standard: SELECT DISTINCT date, loc FROM results, temperatures WHERE results.id = temperatures.id AND value = temperature; Or (more legible, less efficient): SELECT date, loc FROM results WHERE value in ( SELECT temperature FROM temperatures WHERE temperatures.id = results.id ); Don't forget to create and index on the ID field. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Sun, 9 May 1999, Chris Bitmead wrote: > Simon Drabble wrote: > > > I was kinda hoping to do it without resort to (external) > > functions, since I cannot guarantee that Postgres will be the > > target DBMS > > Do other SQL databases even have arrays? I kinda assumed that they were > a Postgres thing in any case. This is a good point :) I was anticipating future DBMS's which may or may not have arrays. > > > COPYRIGHT file in the top-level postgres directory. Does this mean the > > contribs are covered by the same terms, so I can distribute the > > array_iterator code with my app provided I include the copyright notice? > > Well, that is the assumption I would be working under. > K, thanks. Simon. -- "When a German dwarf dances with the butcher's son.." -- Tom Waits Simon Drabble Somewhere in cyberspace simond@foxlink.net
On Sun, 9 May 1999, Herouth Maoz wrote: > At 22:42 +0300 on 08/05/1999, Simon Drabble wrote: > > > > I'd rather not have to code a function for something which, it seems to > > me, should be part of the installation. The application is eventually > > intended for several platforms all of which might be running different > > DBMS's, so I'd like to know if there's a common SQL way to do this and if > > so if Postgres supports it. > > If you want to do it in the SQL way, you won't use arrays. They are, at > least in this use, contrary to the relational model. > > What you really should do is have two tables: > > One (results) should have: > id (primary key) > date > location > > The other (temperatures) should have > id (foreign key into the above table) > temperature > > You enter each temperature into the second table along with the id of the > record to which it belongs. And then your query is super standard: > > SELECT DISTINCT date, loc > FROM results, temperatures > WHERE results.id = temperatures.id > AND value = temperature; > > Or (more legible, less efficient): > > SELECT date, loc > FROM results > WHERE value in ( > SELECT temperature > FROM temperatures > WHERE temperatures.id = results.id > ); > > Don't forget to create and index on the ID field. > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma > Hmm. I had thought of doing it this way. When I was investigating possible solutions I thought arrays looked mighty elegant, but it seems that since they are non-standard I will have to use a separate table, as you outlined above. Thanks to you and Chris for your help. Simon. -- "When a German dwarf dances with the butcher's son.." -- Tom Waits Simon Drabble Somewhere in cyberspace simond@foxlink.net