Re: Add pg_ownerships and pg_privileges system views

On 2024-Oct-20, Alvaro Herrera wrote:

>         SELECT
>             pg_shdepend.classid,
>             pg_shdepend.objid,
>             pg_shdepend.objsubid,
>             identify.*,
>             aclexplode.*
>         FROM pg_catalog.pg_shdepend
>         JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid =
pg_shdepend.dbid
>         JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid AND pg_shdepend.refclassid =
'pg_authid'::regclass,
>         LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS
identify,
>         LATERAL
pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid))AS aclexplode
 
>         WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
>                    FROM pg_database pg_database_1
>                   WHERE pg_database_1.datname = current_database()))
>     ) AS a ;

... actually, the "AND pg_shdepend.dbid = ( SELECT pg_database_1.oid
...)" part of this is useless, because you already had that in the ON
condition of the original join to pg_database.  So, apologies for the
noise there.  TBH I don't see why you put that in the JOIN ON condition
instead of WHERE, but anyway you don't need to add a new condition for
it.  I guess I'd do it like this for clarity:

         FROM pg_catalog.pg_shdepend
         JOIN pg_catalog.pg_database ON pg_database.oid = pg_shdepend.dbid
         JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid
         LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid) AS
identify,
         LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid, pg_shdepend.objid,
pg_shdepend.objsubid))AS aclexplode
 
         WHERE pg_shdepend.deptype = 'a' AND
     pg_shdepend.refclassid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND
     pg_database.datname = pg_catalog.current_database()

... but since these are inner joins, this might be a matter of style.
(I did add a couple of schema-qualifications there.)

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Debido a que la velocidad de la luz es mucho mayor que la del sonido,
 algunas personas nos parecen brillantes un minuto antes
 de escuchar las pelotudeces que dicen." (Roberto Fontanarrosa)