Forum OpenACS Q&A: Response to Can this be done in PostgreSQL

Collapse
Posted by Jonathan Ellis on
Here is one simple way.
select rr.req_id,
       case when exists (select 1 from req_approvals ra where ra.req_id = rr.req_id and ra.approved = 'f') then 'f'
            when exists (select 1 from req_approvals ra where ra.req_id = rr.req_id and ra.approved is null) then null
            when exists (select 1 from req_approvals ra where ra.req_id = rr.req_id and ra.approved = 't') then 't'
            else null
       end  as approved
from req_req rr
Since CASE expressions test their clauses from first to last this should do what you describe, i.e., first check for a false, then check for a null, and finally check for a true. You could probably improve performance by writing a procedure that uses a cursor to go through the matching req_approval entries, so you don't have to scan three times in worst case.

(BTW, postgresql also supports a boolean type.)