Hi allI have created following statement to get the ordered parameter list of functions. I use UNNEST to get rows from array. This works fine but I am not sure whether the ordering remains in the later use. Background is PL/pgSQL to revoke grants to get a pristine start for granting. As the order of the parameter is important, I should use a statement that returns the properly ordered list of parameters. Maybe I did take a wrong turn and one can achieve this simpler.
Suggestions are very welcome. Kind regards Thiemo with PRO_UNNESTED_TYPES as( select oid as PROOID, PRONAME, unnest(PROARGTYPES) as PROARGTYPE, PRONAMESPACE, PROOWNER from PG_CATALOG.PG_PROC ) select P.PRONAME, string_agg( T.TYPNAME, ', ' ) as PARAMETER_LIST_STRING, G.GRANTEE from PRO_UNNESTED_TYPES P inner join PG_CATALOG.PG_TYPE T on P.PROARGTYPE = T.OID inner join PG_CATALOG.PG_NAMESPACE N on P.PRONAMESPACE = N.OID inner join INFORMATION_SCHEMA.ROUTINE_PRIVILEGES G on -- copied from INFORMATION_SCHEMA.ROUTINE_PRIVILEGES source -- as seen in DBeaver 4.3.2 ( ( P.PRONAME::text || '_'::text )|| P.PROOID::text )::INFORMATION_SCHEMA.SQL_IDENTIFIER = G.SPECIFIC_NAME where N.NSPNAME = current_user and G.GRANTEE != current_user group by P.PROOID, P.PRONAME, G.GRANTEE order by P.PRONAME asc, G.GRANTEE asc, PARAMETER_LIST_STRING asc; -- Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC
<<attachment: thiemo.vcf>>