In-Reply-To: <20050415092227.GA24135@computing-services.oxford.ac.uk>
References: <20050415092227.GA24135@computing-services.oxford.ac.uk>
Mime-Version: 1.0 (Apple Message framework v619.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <e1812e3e63292f91f9285e81126ce795@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: pgsql-sql@postgresql.org
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: Combining values in a column
Date: Fri, 15 Apr 2005 06:12:11 -0400
To: Stephen Quinney <stephen.quinney@computing-services.oxford.ac.uk>

See below for creating aggregates:

http://www.postgresql.org/docs/current/static/xaggr.html

But, there is a useful function built-in, bit_and, that does what you  
want:

http://www.postgresql.org/docs/current/static/functions- 
aggregate.html#FUNCTIONS-AGGREGATE-TABLE

create table testint (
	myint int
);
CREATE TABLE
insert into testint values (31);
INSERT 428988938 1
insert into testint values (511);
INSERT 428988939 1
select bit_and(myint) from testint;
  bit_and
---------
       31
(1 row)


On Apr 15, 2005, at 5:22 AM, Stephen Quinney wrote:

> I have a query which returns a single column of integers which I want
> to combine together with &, "bitwise AND". Basically it's a simple
> list of access levels that a user might have and I want to merge the
> list into one value. There can be zero, one or more values in the list
> so I have to be able to cope with the no-results .
>
> For example
>
>  access
> --------
>      31
>     511
>
> Would lead to 31 & 511 which gives 31.
>
> I guess really this can lead to the more generic question. How do I
> apply a general function to combine the values, if I, for example,
> wanted to write my own aggregate function like sum?
>
> Thanks in advance,
>
> Stephen Quinney
>
>
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>