Thread: Re: [GENERAL] Selecting from arrays

Re: [GENERAL] Selecting from arrays

From
Simon Drabble
Date:
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



Re: [GENERAL] Selecting from arrays

From
Chris Bitmead
Date:
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

Re: [GENERAL] Selecting from arrays

From
Simon Drabble
Date:
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


Re: [GENERAL] Selecting from arrays

From
Chris Bitmead
Date:
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

Re: [GENERAL] Selecting from arrays

From
Herouth Maoz
Date:
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



Re: [GENERAL] Selecting from arrays

From
Simon Drabble
Date:
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


Re: [GENERAL] Selecting from arrays

From
Simon Drabble
Date:
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