On Thu, 20 Jun 2024 at 02:33, Joel Jacobson <joel@compiler.org> wrote:
On Wed, Jun 19, 2024, at 16:23, Isaac Morland wrote: > I have no idea how often this would be useful, but I wonder if it could > work to have overloaded single-parameter versions for each of > regprocedure (pg_proc.proacl), regclass (pg_class.relacl), …. To call, > just cast the OID to the appropriate reg* type. > > For example: To get the ACL for table 'example_table', call pg_get_acl > ('example_table'::regclass)
+1
New patch attached.
I've added overloaded versions for regclass and regproc so far:
\df pg_get_acl List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------+------------------+------------------------+------ pg_catalog | pg_get_acl | aclitem[] | classid oid, objid oid | func pg_catalog | pg_get_acl | aclitem[] | objid regclass | func pg_catalog | pg_get_acl | aclitem[] | objid regproc | func (3 rows)
Those were just examples. I think for completeness there should be 5 overloads:
[input type] → [relation.aclattribute]
regproc/regprocedure → pg_proc.proacl
regtype → pg_type.typacl
regclass → pg_class.relacl
regnamespace → pg_namespace.nspacl
I believe the remaining reg* types don't correspond to objects with ACLs, and the remaining ACL fields are for objects which don't have a corresponding reg* type.
In general I believe the reg* types are underutilized. All over the place I see examples where people write code to generate SQL statements and they take schema and object name and then format with %I.%I when all that is needed is a reg* value and then format it with a simple %s (of course, need to make sure the SQL will execute with the same search_path as when the SQL was generated, or generate with an empty search_path).
