Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I perform an AND on an unknown number of booleans in postgresql?

Tags:

I have a table with a foreign key and a boolean value (and a bunch of other columns that aren't relevant here), as such:

CREATE TABLE myTable
(
  someKey integer,
  someBool boolean
);

insert into myTable values (1, 't'),(1, 't'),(2, 'f'),(2, 't');

Each someKey could have 0 or more entries. For any given someKey, I need to know if a) all the entries are true, or b) any of the entries are false (basically an AND).

I've come up with the following function:

CREATE FUNCTION do_and(int4) RETURNS boolean AS
$func$
declare
    rec record;
    retVal boolean = 't'; -- necessary, or true is returned as null (it's weird)
begin
    if not exists (select someKey from myTable where someKey = $1) then
        return null; -- and because we had to initialise retVal, if no rows are     found true would be returned
    end if;

    for rec in select someBool from myTable where someKey = $1 loop
        retVal := rec.someBool AND retVal;
    end loop;

    return retVal;
end;
$func$ LANGUAGE 'plpgsql' VOLATILE;

... which gives the correct results:

select do_and(1) => t
select do_and(2) => f
select do_and(3) => null

I'm wondering if there's a nicer way to do this. It doesn't look too bad in this simple scenario, but once you include all the supporting code it gets lengthier than I'd like. I had a look at casting the someBool column to an array and using the ALL construct, but I couldn't get it working... any ideas?