Skip site navigation (1) Skip section navigation (2)

Re: Array aggregation. Was: PostgreSQL Final Release ... Monday?

From: mlw <markw(at)mohawksoft(dot)com>
To: Hannu Krosing <hannu(at)krosing(dot)net>
Cc: "Marc G(dot) Fournier" <scrappy(at)hub(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Array aggregation. Was: PostgreSQL Final Release ... Monday?
Date: 2002-01-30 23:50:49
Message-ID: 3C5886D9.ECEE93C0@mohawksoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
It is used as an aggregation function. It only works on integers, and assumes
sizeof(int) == sizeof(void *)

There are two functions:

int_array_aggregate(int4)
and 
int_array_enum(int4[])


One creates an integer array as:

create table tst as select some_field, int_array_aggregate(int_field) as field
from table group by some_field;

This will poduce one row for all the unique "some_fields" with an array of all
"int_field" associated.

To extract this, you use int_array_enum(int_array); whic returns multiple
results.





Hannu Krosing wrote:
> 
> On Thu, 2002-01-31 at 02:36, mlw wrote:
> >
> > I submitted a "contrib" project for integer array
> > aggregation/enumeration. I didn't see it in rc2.
> >
> 
> Some general questions about arrays  -
> 
> 1. Could this aggregation/enumeration be done in a generic way, so
>    that you could aggregate over any array ?
> 
>    The only generic function I currently know of is count(), but it
>    is generic only on argument side, i.e count(any) returns always
>    integer.
> 
> 2. Is there a way to define a function such that
> 
>   declare make_array(any) returns any[] ?
> 
> 3. Also, can I prescribe order of aggregation (aggregation applied
>    _after_ ORDER BY) that would act in a way similar to HAVING .
> 
> 4. what arguments must I give to array_in so that it produces an
>    array of specific kind ? I tried some more obvious things and
>    really got nowhere.
> 
> --------------
> Hannu
> 
> PS. I attach an alpha-contrib ofPL/PgSQL code to compare intarrays.
> I have not had time to tie these ops to indexes
> 
> --------------
> Hannu
> 
>   -------------------------------------------------------------------------------
>                      Name: ARRAY_COMP.SQL
>    ARRAY_COMP.SQL    Type: text/x-sql
>                  Encoding: quoted-printable

In response to

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2002-01-30 23:55:20
Subject: Re: RFD: schemas and different kinds of Postgres objects
Previous:From: Bill StudenmundDate: 2002-01-30 23:41:09
Subject: Re: RFD: schemas and different kinds of Postgres objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group