Sub-select testing group membership (Arrays)
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?
The *= operator is not part of the standard Postgres build. You have to add
an explicit extension from the contributions directory of the source tree.
Did you do this? It seems to me that Postgres is not recognizing that
operator.
Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
Show quoted text
From: Neal Lindsay <neal.lindsay@peaofohio.com>
Date: Wed, 03 Oct 2001 08:52:49 -0400
To: pgsql-general@postgresql.org
Subject: [GENERAL] Sub-select testing group membership (Arrays)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.contactidAs 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 castWhen 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?
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly