Hello, all.
I'm writing because I may have found a bug which emerged somewhere after version 9.3 and at or before 9.6.
While experimenting with some automation for a DBA, I found that expressions created in PLPgSQL using:
SELECT INTO execstring
format(
'GRANT EXECUTE ON FUNCTION %I.%I(%s) TO PUBLIC',
nspname,
proname,
pg_get_function_identity_arguments(pg_proc.oid)
)
FROM
pg_proc
JOIN
pg_namespace ON pronamespace = pg_namespace.oid
-- WHERE
-- more stuff
were failing in some cases due to syntax error in the generated SQL.
According to the System Information Functions docs, pg_get_function_identity_arguments(OID) should simply "get argument list to identify a function (without default values)", but one example of how it behaves strangely is that:
SELECT pg_get_function_identity_arguments('pg_catalog.percentile_disc(DOUBLE PRECISION[], ANYELEMENT)'::REGPROCEDURE)
yields
'double precision[] ORDER BY anyelement'
which leaves you with a bad expression like:
GRANT EXECUTE ON FUNCTION pg_catalog.percentile_disc(double precision[] ORDER BY anyelement) TO public;
Obviously, the above leads to a syntax error.
Version: PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Presumably, the approach I showed is old and no longer in use. I've found a way around needing to use pg_get_function_identity_arguments() myself. However, the behavior described above still represents a little pitfall. Is this actually a bug or do the docs perhaps need to be more clear on what pg_get_function_identity_arguments() is meant for (and possibly recommend some alternate way to generate function-signatures)?
Regards,
- Patrick O'Toole
Application Developer
Wyoming Natural Diversity Database
UW Berry Biodiversity Conservation Center
Department 3381, 1000 E. University Av.
Laramie, WY 82071
P: 307-766-3018