From: Sean Davis <sdavis2@mail.nih.gov>
To: Stephen Quinney <stephen.quinney@computing-services.oxford.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Combining values in a column
Date: Fri, 15 Apr 2005 06:12:11 -0400
Message-ID: <e1812e3e63292f91f9285e81126ce795@mail.nih.gov> (raw)
In-Reply-To: <20050415092227.GA24135@computing-services.oxford.ac.uk>
References: <20050415092227.GA24135@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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sdavis2@mail.nih.gov, stephen.quinney@computing-services.oxford.ac.uk
Subject: Re: Combining values in a column
In-Reply-To: <e1812e3e63292f91f9285e81126ce795@mail.nih.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox