Sub-select testing group membership (Arrays)

From: Neal Lindsay <neal(dot)lindsay(at)peaofohio(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Sub-select testing group membership (Arrays)
Date: 2001-10-03 12:52:49
Message-ID: 3BBB0A21.3000403@peaofohio.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am trying to us subselects to test if users are in certain groups.
Here is my actual query:

SELECT e.initials, c.ln, c.mi, c.fn, c.pre, c.post, u.usesysid, (SELECT
(grolist *= u.usesysid) FROM pg_group WHERE groname = 'manager_group')
AS in_managers, (SELECT (grolist *= u.usesysid) FROM pg_group WHERE
groname = 'admin_group') AS in_admin FROM (pg_user AS u INNER JOIN
employee AS e ON u.usesysid = e.fusesysid) INNER JOIN contact AS c ON
e.fcontactid = c.contactid

As you can see, I want the fields in_admin and in_managers to to contain
boolean values that I can use to enable certain features in my
front-end. However, when I execute the query I get this error:
ERROR: Unable to identify an operator '*=' for types '_int4' and 'int4'
You will have to retype this query using an explicit cast

When I try using explicit casts, I get the following error:
ERROR: Cannot cast type '_int4' to 'int4'

or if I try it the other way:
ERROR: Cannot cast type 'int4' to '_int4'

What do I need to do to test these arrays for membership?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Boes 2001-10-03 14:13:40 Re: Column existence - how to check?
Previous Message Maxim 2001-10-03 11:51:05 returned multiple data from function