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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With