How can I feed back a subquery as a boolean column in PostgreSQL?

Question

We store our accounts information in a PostgreSQL database.

Accounts are in the "accounts" table, groups in the "grp" table, and they're tied together by the "account_grp" table, which maps account_id to grp_id.

I'm trying to craft a query which will give me a view which lets me search for whether members of one group are members of another group, i.e. I want an "is_in_foobar_group" column in the view, so I can SELECT * FROM my_view WHERE grp_id = 1234; and get back

username | is_in_foobar_group | grp_id 
---------+--------------------+-------
bob      | true               | 1234
alice    | false              | 1234

The foobar bit is hardcoded, and will not need to change.

Any suggestions?

Answer 1

Simpler, faster, more convenient:

WITH x AS (SELECT 1234 AS foobar)  -- optional, to enter value only once
SELECT a.username
      ,EXISTS (
         SELECT 1 FROM account_grp g 
         WHERE  g.account_id = a.account_id
         AND    g.grp_id = x.foobar
         ) AS is_in_foobar_group
      ,x.foobar AS grp_id
FROM  accounts a, x

Answer 2

Maybe using the EXISTS operator would help: http://www.postgresql.org/docs/9.2/static/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS

I'm not sure you can use it in a SELECT statement, and I don't have a PostgreSQL instance to check it. Worst case you'll have to do 2 queries, something like:

SELECT username, true, grp_id 
FROM accounts a INNER JOIN account_grp g1 on a.account_id = g.account_id
WHERE EXIST (SELECT 1 FROM account_grp g2 
    WHERE g2.account_id = a.account_id and g2.grp_id = [foobar])
UNION
SELECT username, false, grp_id 
FROM accounts a INNER JOIN account_grp g1 on a.account_id = g.account_id
WHERE NOT EXIST (SELECT 1 FROM account_grp g2 
    WHERE g2.account_id = a.account_id and g2.grp_id = [foobar])