Re: [GENERAL] Selecting from arrays

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