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)