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