ddevienne@gmail.com wrote:
bryn@yugabyte.com wrote:…Then I did this:
with c as (
select
proname::text as name,
pronamespace::regnamespace::text as schema,
aclexplode(proacl) as "aclexplode(proacl)"
from pg_catalog.pg_proc)
select "aclexplode(proacl)" from c
where name = 'q' and schema = 's';
This is the result:
aclexplode(proacl)
-----------------------------
(1494148,0,EXECUTE,f)
(1494148,1494148,EXECUTE,f)
(1494148,1494150,EXECUTE,f)
`aclexplode` is a table-valued function, so you normally use it in the
FROM clause.
Here's how I use it on schemas for example:
```
select nspname as name,
nspowner::regrole::text as owner,
grantor::regrole::text,
grantee::regrole::text,
privilege_type, is_grantable
from pg_namespace
left join lateral aclexplode(nspacl) on true
where ...
order by nspname
```
Thank you very much for the tip and for the code example, Dominique. Yes, my SQL was poorly written. I wanted just a simple proof of concept that "aclexplode()" lets me access the individual values that the "proacl" column represents as an array of "aclitem" records without needing to parse text strings like "z=X/x". I'd started to picture writing my own function to do what "aclexplode()" does. But Julien Rouhaud told me about the built-in for the purpose I needed before I'd had time to give my own function any thought.
I should have at least moved my invocation of "aclexplode()" out of the CTE. But, of course, for an approach that finds many "pg_proc" rows, I'll need a proper, robust approach like you showed.