Re: Add pg_ownerships and pg_privileges system views

On Mon, Oct 21, 2024, at 11:42, Alvaro Herrera wrote:
> ... 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.)

Ahh, right, that's nicer, thanks for fixing.

New patch attached.

I also fixed pg_ownerships in the same way, moving the `pg_catalog.pg_database.datname = pg_catalog.current_database()` to the WHERE clause instead.

/Joel