Re: [GENERAL] Selecting from arrays

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