I am trying to work with PostgreSQL CTE to deal with a hierarchical
question. I have a group who's members are people or other groups.
Groups are identified by negative number and real people with a positive
number. The question, who are all the members of the group?
CREATE TABLE userroles (
rid INTEGER rid, -- Role ID
ruid INTEGER, -- Role or user ID
PRIMARY KEY(ruid, rid)
);
Role -1 has users 1, 2, 3, 4 and role -2
Role -2 has users 5, 6 and role -3
Role -3 has users 8 and 9
To find all the users of role -1 I can use the following in PSQL
WITH RECURSIVE member_role_uids(rid, ruid) AS (
SELECT rid, ruid
FROM rr_userroles
WHERE rid = -1
UNION
SELECT rr.rid, rr.ruid
FROM rr_userroles AS rr, member_role_uids AS mru
WHERE rr.rid = mru.ruid
)
SELECT * from member_role_uids;
I get the wonderful output of
rid | ruid
-----+------
-1 | 1
-1 | 2
-1 | 3
-1 | 4
-1 | -2
-2 | 5
-2 | 6
-2 | -3
-3 | 8
-3 | 9
(10 rows)
This is good enough for me. However in psycopg2 I get None returned
from cursor.execute(query)
Goggling about didn't give me any leads, or I'm using the wrong terms.
I'm hoping someone else has run across this or can point me in the right
direction.
psql 9.1.7
psycopg2 2.4.5 (dt dec mx pq3 ext)