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)
