Arrays

[Symbolics]

I am trying to reproduce the \du function of pgsql, which is implemented (in GreenPlum) as:

********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolcreaterextgpfd
, r.rolcreatewextgpfd
, r.rolcreaterexthttp
, r.rolcreaterexthdfs
, r.rolcreatewexthdfs
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************

However I am having a bit of trouble with the nested query (ARRAY). Is there any existing POMO-magic that can reproduce this query in s-sql?

[Symbolics]

Wow @sabracrolleton, you have been busy! Job well done. I am now 90 commits behind. At least I am back working with PostModern.

The need for array has come back again. Any chance I can nudge this one towards the top of the queue?

[sabracrolleton]

Does this work for you?

(query (:order-by
        (:select 'r.rolename 'r.rolsuper 'r.rolinherit
                 r.rolcreaterole 'r.rolcreatedb 'r.rolcanlogin
                 r.rolconnlimit
                 (:as (:array
                       (:select 'b.rolename
                                :from (:as 'pg_catalog.pg-auth-members 'm)
                                :inner-join (:as 'pg-catalog.pg-roles 'b)
                                :on (:= 'm.roleid 'b.oid)
                                :where (:= 'm.member 'r.oid )))
                      'memberof)
                 'r.rolcreaterextgpfd
                 'r.rolcreatewextgpfd
                 'r.rolcreaterexthttp
                 'r.rolcreaterexthdfs
                 'r.rolcreatewexthdfs
                 :from (:as 'pg-catalog.pg-roles 'r))
        1))

[sabracrolleton]

:array[] operator added in today's commit. Also see documentation on the use of postgresql arrays in the file array-notes.html in the doc directory.